Creating Financial Calendar - Decoding Date and Calendar 1-5 - Power BI Turning 5 Celebration Series

[amitchandak]

Problem Description: 

A financial calendar that starts from any month of the year, and Month No, Qtr No should follow the start of the calendar month.

Solution Overview:

We are going to create 12 Calendars. Every Calendar is going to start from a different month.  We are going to use DAX Calendar, startofyear, endofyear, and a few other functions to create the desired Calendars.

Challenge:

Both startofyear and endofyear do not work best for the year that ends in February. They create a problem with the year around leap year. So, I used some custom code to handle that. 

Solution:

As Year Ending February poses a challenge, let's start with that Start from March.

Calendar

Date = CALENDAR(date(2015,03,01),Date(2021,02,28))

Year

Start Of Year = 
var _eoy = if(month([Date])<=2,year([Date])-1,year([Date]))
return
date(_eoy,3,1)

End of Year = 
var _eoy = if(month([Date])<=2,year([Date]),year([Date])+1)
return
if(mod(_eoy,4)=0,date(_eoy,2,29),ENDOFYEAR('Date'[date],"2/28"))

Fin Year = year('Date'[Start Of Year])
FY = "FY "&FORMAT('Date'[Start Of Year],"YYYY") & "-" &FORMAT('Date'[End of Year],"YYYY")

Month

Month Year = FORMAT('Date'[Date],"MMMM YYYY")
Month Year 2 = FORMAT('Date'[Date],"MMM-YYYY")
Month Year Sort = var _m = DATEDIFF('Date'[Start Of Year], 'Date'[Date],MONTH)+1 return if(_m<10 , [Fin Year] &"0"&_m,[Fin Year] &_m)
Month = FORMAT('Date'[Date],"MMMM")
Month Sort = DATEDIFF('Date'[Start Of Year], 'Date'[Date],MONTH)+1

Quarter

Qtr No = QUOTIENT(DATEDIFF('Date'[Start Of Year], 'Date'[Date],MONTH),3)+1
Qtr Year = "Q" & 'Date'[Qtr No] & " "& 'Date'[FY]
Qtr Start Date = 
var _st ='Date'[Start Of Year]
var _Q = QUOTIENT(DATEDIFF([Start Of Year], 'Date'[Date],MONTH),3)*3
return date(year(_st),month(_st)+_Q,1)
Qtr Year Sort = year('Date'[Start Of Year])&'Date'[Qtr No]
Qtr Rank = RANKX(all('Date'),[Qtr Start Date],,ASC,Dense)

Qtr Rank can be used to find the Last Qtr Data.

All other Calendar will work in the Same Manner as given below

Calendar - April to March

Date = CALENDAR(date(2014,04,01),Date(2021,03,31))

Year

Start Of Year = STARTOFYEAR('Date'[Date],"3/31")
End of Year = ENDOFYEAR('Date'[Date],"3/31")
Fin Year = year('Date'[Start Of Year])
FY = "FY "&FORMAT('Date'[Start Of Year],"YYYY") & "-" &FORMAT('Date'[End of Year],"YYYY")

Month

Month Year = FORMAT('Date'[Date],"MMMM YYYY")
Month Year 2 = FORMAT('Date'[Date],"MMM-YYYY")
Month Year Sort = var _m = DATEDIFF('Date'[Start Of Year], 'Date'[Date],MONTH)+1 return if(_m<10 , [Fin Year] &"0"&_m,[Fin Year] &_m)
Month = FORMAT('Date'[Date],"MMMM")
Month Sort = DATEDIFF('Date'[Start Of Year], 'Date'[Date],MONTH)+1

Quarter

Qtr No = QUOTIENT(DATEDIFF('Date'[Start Of Year], 'Date'[Date],MONTH),3)+1
Qtr Year = "Q" & 'Date'[Qtr No] & " "& 'Date'[FY]
Qtr Start Date = DATEADD(STARTOFYEAR('Date'[Date],"3/31"),QUOTIENT(DATEDIFF('Date'[Start Of Year], 'Date'[Date],MONTH),3)*3,MONTH)
Qtr Year Sort = year('Date'[Start Of Year])&'Date'[Qtr No]
Qtr Rank = rankx(ALL('Date'),'Date'[Qtr Start Date],,ASC,Dense)

Link:

You can find all 12 Calendars here: https://www.dropbox.com/sh/lt05p9angg10qyg/AABB20wez_qV5dHMAOVa4hv9a?dl=0

You can also find three calendars attached to this Blog.

My Previous Blogs -

Week Is Not So Weak,

Date Difference Across Table- Direct Query Mode, Decoding Direct Query - Time Intelligence, Winner Coloring on MAP, HR Analytics, Power BI Working with Non-Standard Time Periods and Comparing Data Across Date Ranges
Connect on LinkedIn

Aug_2_Jul_Qtr_Cal.pbix

March_2_Feb_Qtr_Cal.pbix

Apr_2_MAr_Qtr_Cal.pbix