ChandakAmit
Published in

ChandakAmit

Cheat Sheet — Calendar of any Standard Month — Just one variable apart

Objective — To have a calendar for any month by just changing only one variable, end month

Solution: The solution should use the month as a variable and should create a calendar whose start and end date are based on the end month provided.

The calendar should generate the month start date, month end date, FY month number, quarter start Date, quarter end Date, and FY week

I created a script. For the calendar start date, I have logic using the year 2018. For the calendar end date, I simply try to reach the end date using the standard calendar end date and today, so you might see a few years have one additional year of data

There are a few tables generated using addcolumns. I tried to minimize the formula's repetition.

_FYENDMonth is the variable that needs to be changed

Calendar Code

Date = var _max = Today() 
var _FYENDMonth =3
var _FYRemain = 12 -_FYENDMonth
var _end = EOMONTH(_max,12-MONTH(_max)+_FYENDMonth)
var _cal =
ADDCOLUMNS( CALENDAR(DATE(2018,_FYENDMonth+1,01), _end)
, “Month Year” , FORMAT([Date], “MMM-YYYY”)
, “Month year Sort” , Year([Date])*100 + month([date])
, “month start date” , EOMONTH([Date],-1)+1
, “month end date” , EOMONTH([Date],0)
, “FY year Start Date”, if(month([Date])<=_FYENDMonth, EOMONTH([Date],-1*month([Date])- _FYRemain) ,EOMONTH([Date],-1*month([Date])+_FYENDMonth ) )+1
, “FY year End Date”, if(month([Date])<=_FYENDMonth, EOMONTH([Date],_FYENDMonth-1*month([Date])) ,EOMONTH([Date],12–1*(month([Date])- _FYENDMonth) ))
,”Weekday name”, format([date], “dddd”)
, “Weekday”, WEEKDAY([Date],2)
,”Start Week Date” , [Date] -1* WEEKDAY([Date],2) +1
,”End Week Date” , [Date] + 7 -1* WEEKDAY([Date],2)
, “Cal Year”,year([date]))
Var _cal_cal2 =AddColumns( _cal,
“FY” , Year([FY year Start Date]),
“FY Month” , Datediff([FY year Start Date], [month start date],MONTH)+1 ,
“FY Qtr” , Quotient(Datediff([FY year Start Date], [month start date],MONTH),3)+1 ,
“FY Qtr Start” , Eomonth([FY year Start Date],Quotient(Datediff([FY year Start Date], [month start date],MONTH),3)*3–1)+1 ,
“FY Qtr End” , Eomonth([FY year Start Date],Quotient(Datediff([FY year Start Date], [month start date],MONTH),3)*3+2) ,
“FY Week Start”,[FY year Start Date] -WEEKDAY([FY year Start Date],2)+1
)
//var _end = ENDOFYEAR(Sales[Sales Date])
return
ADDCOLUMNS(_cal_cal2,
“FY Year Month” , [FY]*100 + [FY Month]
,”FY Week” , QUOTIENT(DATEDIFF([FY Week Start],[Date],day),7)+1
,”FY Week No” , [FY]*100 + QUOTIENT(DATEDIFF([FY Week Start],[Date],day),7)+1
)

Download the file from — here

Click Here to access all my blogs and videos in a jiffy via an exclusive visual glossary using Power BI.
Please like, share, and comment on these. Your suggestions on improvement, challenges, and new topics will help me explore more.

You Can watch my Power BI Tutorial Series on My Channel, Subscribe, Like, and share

--

--

--

Business Intelligence, Analytics, ML , Data Science, BI tools and technologies, Power BI, Tableau, Incorta, Qilk Sense, Quicksight, Python, R, Spark, Kafka, Solr, Elastic Search

Recommended from Medium

Double Prices or Double Standards ?

REST Vs GRAPHQL Vs GRPC

The Ultimate Prize

Increase your AWS CDK Lambda development speed by testing locally with AWS SAM

GIF demonstrates using SAM to test Lambda + API Gateway generated with CDK.

Tatum now supports Flow!

An Overview of WebAssembly

Minti on Solana

Invoking Stored Procedures with Python

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
Amit Chandak

Amit Chandak

Amit has 18+ years of experience in Business Intelligence, Analytics, Data Science. He is Chief Analytics Officer at Kanerika & Power BI community Super User

More from Medium

Split column and Combine Columns

How to Perform the Excel SUMPRODUCT Function in Power BI using DAX Query

Using Power Query to convert Repeating Rows to Columns

Transform Repeating Rows to a Column format table

A Beginner’s Guide to Power BI Desktop