Creating Financial Calendar – Decoding Date and Ca…

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])

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

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

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 –

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

Source link