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)+_date
var _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
)
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
