Written By Rick de Groot

Rick is the founder of BI Gorilla. He believes learning is one of life's greatest pleasures and shares his knowledge to help you improve your skills.

In this article, we’ll go through the steps of creating a fiscal calendar in Power Query, including how to compute the relevant fields such as Fiscal Year, Fiscal Month, and Fiscal Quarter, as well as the dates that correspond to the start and end of these periods. We’ll go over everything you need to know to set up your fiscal calendar easily.

Table of contents

1. What is a Fiscal Calendar?

We usually think of a calendar year as running from January 1st to December 31st. Well, a fiscal calendar is a bit different.

A fiscal calendar is a calendar where the year does not always start on January 1st. It can start on any month within the year. And just like a regular calendar year, a fiscal calendar is made up of 12 months. But instead of calling it a calendar year, we call it a fiscal year.

Within that fiscal year, we break it down even further. We divide the fiscal year into four parts, called fiscal quarters. Each quarter is made up of three months, the fiscal months.

2. Why is a Fiscal Calendar Important?

As long as your reporting uses a regular calendar year, life is good. Time intelligence functions in Power BI work, you can easily find the end and start of the year, and your monthly numbers follow the regular conventions.

However, if you want to report on fiscal calendars, things can get a bit more tricky. You will either have to account for the relevant year-end date in all of your DAX measures or resort to a custom Fiscal Date Table. This article helps you in creating the latter.

3. Fiscal Calendar Fields

So, let’s say you have a bunch of dates in your calendar, and you want to figure out how to create a fiscal year. I’ll show you how to do that in this article.

3.1. Creating a Fiscal Year

To create a fiscal year in Power Query M, it’s important to know when your fiscal year starts and ends. It really depends on the company’s accounting, and there’s no general rule for it.

One way to make it easier is by creating a parameter. This parameter will tell us the month when your fiscal year ends. For example, let’s say your fiscal year always ends in July. So, your FiscalYearEndMonth parameter would be set to 7.

With that information, we can use Power Query M to create the Fiscal Year number:

=  Date.Year( Date.AddMonths( [Date], 12 - FiscalYearEndMonth )

// --- or alternatively

Date.Year( [Date] ) + Int64.From( Date.Month( [Date] ) > FiscalYearEndMonth )

That was relatively easy. Next, we want to compute a Fiscal Month.

3.2. Creating a Fiscal Month

Okay, so now that we’ve figured out how to create a Fiscal Year, let’s move on to creating the Fiscal Month. This part is actually pretty simple, and we’ll be using that parameter we set up earlier to figure out when the fiscal year ends.

First, we’ll grab the date value, and then we’ll move it back by the number of months in the fiscal year-end parameter. So, let’s say we have a date of August 1st, 2023. If our fiscal year ends in July, we’ll move it back 7 months. That would take us back to January 1st, 2023.

Now, all we have to do is return the month’s value. So, for our example, we’ll get the number 1. This is the Fiscal month of the given date.

= Date.Month( Date.AddMonths( [Date], - FiscalYearEndMonth ) )

And that’s it, we’ve successfully created a Fiscal Month!

3.3. Creating a Fiscal Quarter

Alright, now that we know how to create a Fiscal Month, let’s move on to creating a Fiscal Quarter. This step is pretty simple too.

We’re going to use the Fiscal Month we defined in the previous step to figure out the Fiscal Quarter. All we have to do is divide the Fiscal Month by 3 and round up the result. So if our Fiscal Month is 1, we would divide by 3 and round the result to 1. That would mean we’re in Fiscal Quarter 1. And if our Fiscal Month was 3, we would divide by 3 and still round the result to 1. So we’re still in Fiscal Quarter 1.

Here is the easiest way to create a Fiscal Quarter Number:

= Number.RoundUp( [Fiscal Month] / 3) 

As the dates can be very custom, it can be useful to see a tangible date related to the quarter. The below code allows you to create a Fiscal Quarter Starting Date and Fiscal Quarter Ending Date:

= Date.AddMonths( Date.StartOfQuarter( [Date] ), Number.Mod( FiscalYearEndMonth, 3 ))
// Returns the Fiscal Quarter Starting Date
= Date.EndOfMonth( Date.AddMonths( [Date], [Fiscal Quarter] * 3 - [Fiscal Month] ) )
// Returns the Fiscal Quarter Starting Date

3.4. Creating Fiscal Year Start and End Date

Alright, now that we know how to create a Fiscal Year, Fiscal Month, and Fiscal Quarter, let’s move on to figuring out the starting and ending dates of a Fiscal Year. This is important because it helps us easily spot the Start and End of the Fiscal Year.

= Date.AddMonths( Date.StartOfYear( [Date] ), FiscalYearEndMonth - 12 )
// Returns the Fiscal Year Starting Date
= Date.AddMonths( Date.EndOfYear( [Date] ), FiscalYearEndMonth - 12 )
// Returns the Fiscal Year Ending Date

4. Conclusion

A fiscal calendar is an invaluable asset for your Power BI reporting. When your company has a fiscal year not starting on January 1st, the default time intelligence functions don’t work anymore. You will need to write custom formulas that strongly leverage your Fiscal Date Table. With the help of Power Query M language, you can extract relevant information and create a fiscal calendar that works for your business.

Happy querying!

Share on:
  1. Rick,
    Thanks for this series on calendars; it’s been VERY helpful in my first PQ project. The fiscal year blog only gets me 2/3 to where I need to be. This method works when the fiscal month begins on the first of the month.

    My challenge is that I need to compare financial transactions based on standard calendar with those with fiscal quarters ending on the 21st of December, March, June, and September. What changes are needed to create this unusual fiscal year?

    P.S. I’m basically a beginner with queries and M so I’m stumped.
    –Bruce

    Reply
  2. Hey Rick, thanks for sharing! My organisation uses a 4-4-5 fiscal calendar, but have run into an issue at the moment where we have an extra week this year (Week 53). So Q4 actually has a 4-5-5 configuration… Can this be modified to add an extra week?

    Reply
    • Cian,
      As far as I know, the 4-4-5 fiscal calendars work only with 52 weeks. That means each year you define the start of your retail calendar and start the 4-4-5 week calculations.

      The next year, you do the same. Since this is only used for commercial reporting (and not accounting) this generally is not considered a problem.

      Reply
    • Hi Cian
      One of my first projects in Power Query was to write out a 4-4-5 calendar for my client. I found a way to handle the 53rd week when it comes up. In my example, the first Monday of the calendar year is the start of week 1, so when the year ends on a Monday, there is a 53rd week, but it depends on the rules you have in place.

      The M code is pretty ugly as I was an absolute novice at the time and I need to rewrite it, but I’d be happy to share for ideas!

      Reply
    • David – Thank you very much. It’s been fun making them too. Really tried to bundle different date challenges together in a single place. If any major topics are missing, feel free to suggest.

      Cheers, Rick

      Reply

Leave a comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.