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

[amitchandak]

Series Links

1.
https://community.powerbi.com/t5/Community-Blog/Creating-Financial-Calendar-Decoding-Date-and-Calend...
2.
https://community.powerbi.com/t5/Community-Blog/Any-Weekday-Week-Decoding-Date-and-Calendar-2-5-Powe...
3.
https://community.powerbi.com/t5/Community-Blog/Week-That-Resets-Yearly-Decoding-Date-and-Calendar-3...
4.
https://community.powerbi.com/t5/Community-Blog/Travelling-Across-Workdays-Decoding-Date-and-Calenda...
5 .
https://community.powerbi.com/t5/Community-Blog/Date-as-Dateadd-Decoding-Date-and-Calendar-5-5-Power...

[learning_dax]

Hi @amitchandak! I'm a frequent viewer of your blog posts & Youtube videos. Love the content. I've tried implementing this for a fiscal year calendar that starts June 1 and ends May 31. However, when I plugged in your calculated column formulas there seems to be a lag. 

For example, this pasty May (May 31, 2022) our fiscal year 2022 just ended. Thus, starting June 1 (about a week ago from writing this) our fiscal year 2023 begins. However, in the Calendar Table after using your formulas it still says we are in "Fin Year" 2022 and "FY 2022-2023". It seems to be lagging back a year or so. 

Is this a DAX issue, a sorting issue, or something I am not doing right? I am sure it is an easy fix but I am lost as to what to do. 

[quyenduong]

Hi @amitchandak, if I have a continous data over 10 years (meaning that the data is keep updating every month), are there any ways to make Financial year/month/quarter without defining starting date and end date? Thank you so much