Create a Fiscal Calendar in Power Query

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?
• 2. Why is a Fiscal Calendar Important?
• 3. Fiscal Calendar Fields
  • 3.1. Creating a Fiscal Year
  • 3.2. Creating a Fiscal Month
  • 3.3. Creating a Fiscal Quarter
  • 3.4. Creating Fiscal Year Start and End Date
• 4. Conclusion

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!