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