Cheat Sheet — Calendar that starts with any date of the month — Just two variables apart
Objective — To have a calendar for any date of the month by just changing only two variables, the end month and end date
Solution: The solution should use the month and date as a variable and should create a calendar whose start and end date are based on the end month and date 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.
Variable are
_FYENDMonth — Provide the month in which the calendar will have an end date
_date provide the end day/date of the month
Date = var _max = Today()
var _FYENDMonth =5
var _date = 15
var _MMDD = _FYENDMonth *100 + _Date
var _FYRemain = 12 -_FYENDMonth
var _end = EOMONTH(_max,12-MONTH(_max)+_FYENDMonth)+_datevar _cal =
ADDCOLUMNS( CALENDAR(DATE(2018,_FYENDMonth,_date+1), _end)
, “Month Year” , FORMAT([Date], “MMM-YYYY”)
, “Month year Sort” , Year([Date])*100 + month([date])
, “month start date” , if(day([Date])<=_date, EOMONTH([Date],-2)+_date ,EOMONTH([Date],-1)+_date )+1
, “month end date” , if(day([Date])<=_date, EOMONTH([Date],-1)+_date ,EOMONTH([Date],0)+_date )
, “FY year Start Date”, if(format([Date], “MMDD”)*1<=_MMDD, date(Year([Date])-1,_FYENDMonth,_date) ,date(Year([Date]),_FYENDMonth,_date)) +1
, “FY year End Date”, if(format([Date], “MMDD”)*1<=_MMDD, date(Year([Date]),_FYENDMonth,_date) ,date(Year([Date])+1,_FYENDMonth,_date))
,”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+_date ,
“FY Qtr End” , Eomonth([FY year Start Date],Quotient(Datediff([FY year Start Date], [month start date],MONTH),3)*3+2)+_date ,
“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 Year Qtr” , [FY]*100 + [FY Qtr]
,”FY Qtr Year” , “Q” & [FY Qtr] & “-” &[FY]
,”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
Want to Learn More, Explore these videos and video series.
Complete Power BI Tutorial/Course in Just 11 Hours
Mastering Microsoft Fabric: 30 Videos
Want to Learn SQL: 20 Videos
Mastering Power BI: 230+ Videos
Power BI Concepts- How Power BI Works- 150+ Videos
Migrating from Tableau to Power BI: 30 Videos
DAX for SQL Users: 10 Videos
What works in Power BI Direct Query- 10+ Videos
सीखे Power BI हिंदी में- 60+ Videos
Power BI Hindi On Demand- 10+ Videos
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
