Calendar of any Standard Month - Just one variable apart.
I have shared the script with you to have a Calendar of all 12 months in the past.
Creating Financial Calendar - From Any Month
https://community.powerbi.com/t5/Community-Blog/Creating-Financial-Calendar-Decoding-Date-and-Calend...
But I have now a script, you change the end month and you will get the calendar on any standard month
Change in _FYENDMonth variable. Value between 1-12
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
)
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
