Latest Enterprise DNA Initiatives


5-4-4 Flexible Fiscal Calendar Creation

In the case that anyone follows a 5-4-4 Fiscal Calendar, this code should be of resource to any PBI report that follows this standard.

In order to adjust your calendar, all one would need to do is adjust the following parameters:
(StartDate as date, FiscalYear as number, FiscalYearSequence as number)

This custom code is listed at the bottom of the query and shouldn’t be affected by a leap year.

I am not the original creator of this code.

Thanks,

Nicolas Nouchi

Fiscal Dates Calendar - Power BI.txt (4.8 KB)

3 Likes

Thanks for sharing…

Joe

Super work. Nice one

Thanks for sharing!

Hi Nicolas,

Thanks for sharing this calendar. I’m new to using Power BI and I’ve hit a bit of a wall with dates. I work for a US-based company which uses a 5-4-4 fiscal calendar with the traits below:

  • Fiscal Year always starts on Jan 1

  • The first day of the fiscal year could be any day of the week depending on when Jan 1 falls

  • Fiscal Year always ends on Dec 31

  • The last day of the fiscal year could be any day of the week depending on when Dec 31 falls

  • Although it’s a 5-4-4 calendar, Dec could have 5 weeks (with the last week being a partial week) just depending upon how the days fall, and in most cases it does have 5 weeks. The last time Dec had just 4 weeks was back in 2016

As I mentioned, I’m new to Power BI and my skill level is low

*Any ideas on if/how I could use the calendar you provided

*Is it true that all the buil- in time intelligence functionality is unusable if I use a custom calendar? If that’s the case what are the options for comparing MTD, QTD, YTD for year-over-year actual and actual vs plan performance

As Reference: Here is Fiscal Year 2020

Jan: Jan 1 – Feb 1
Feb: Feb 2 – Feb 29
Mar: Mar 1 – Mar 28
Apr: Mar 29 – May 2
May: May 3 – May 30
Jun: May 31 – Jun 27
Jul: Jun 28 – Aug 1
Aug: Aug 2 – Aug 29
Sep: Aug 30 – Sep 26
Oct: Sep 27 – Oct 31
Nov: Nov 1 – Nov 28
Dec: Nov 29 – Dec 31

Thanks in advance,

Dan