Creating a Fiscal Calendar in Power Query M | 18+ Columns

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.

This article is part of the Date Table series, which consists of:

For a quick glance of what we will cover in the article, make sure to check out:

Table of contents

• 1. What is a Fiscal Calendar?
• 2. Why is a Fiscal Calendar Important?
• 3. Fiscal Year Calculations
  • 3.1. Creating a Fiscal Year
  • 3.2. Fiscal Start and End of Year
• 4. Fiscal Month Calculations
• 5. Fiscal Quarter Calculations
  • 5.1. Fiscal Quarter Number
  • 5.2. Fiscal Month Of Quarter
  • 5.3. Fiscal Quarter Start and End Date
  • 5.4. Composite Fiscal Quarter Values
  • 5.5. Fiscal Day Of Quarter
• 6. Download
• 7. Conclusion
• 8. Frequently Asked Questions

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.

Here’s a quick overview of the fiscal calendar fields we will learn to create in this article:

Let’s find out we can create these.

3. Fiscal Month Calculations

The fiscal month (or period) is an important component of the fiscal calendar. This part is easy to calculate.

3.1. Creating a Fiscal Month Number

If you want to create a fiscal month in Power Query M, you need to know when your fiscal year starts and ends, which varies by company.

It’s therefore helpful to use a parameter to represent the month when your fiscal year ends. For example, if your fiscal year ends in July, you would set the FiscalYearEnd parameter to 7.

To create the fiscal month, we can then adjust the date value based on the number of months in the FiscalYearEnd parameter. This will give us the fiscal month for any given date.

To create the fiscal month number you can use:

Date.Month( Date.AddMonths( [Date], - FiscalYearEnd ) )

Let’s say we have a date of August 1st, 2023, and our fiscal year ends in July. We move the date back 7 months to determine the fiscal month. Here’s the code:

// With fiscal Year End in July, August is the first month, output: 1
Date.Month( Date.AddMonths( #date( 2025, 8, 30 ), - 7) )

This adjustment moves the date back to January 1st, 2023. The fiscal month for August 1st, 2023, is 1. And that’s it, we’ve successfully created a Fiscal Month.

Here’s what that looks like when you apply it to each month of a 2025:

3.2. Adding a Fiscal Month Label

Since it can be hard to differentiate the fiscal month from a regular month number, you may want to add a label to the Fiscal Month. After adding the Fiscal Month, you can do that as follows:

"FM" & Text.PadStart( Text.From( [FiscalMonth] ), 2, "0" )

Applying this to our data looks as follows:

Since start-of-month and end-of-month calculations from the regular calendar also work for the fiscal months, we won’t need fiscal calendar related columns for those. Alright, now that we know how to create a Fiscal Month, let’s move on to creating a Fiscal Quarter.

4. Fiscal Year Calculations

Up next are the various calculations related to fiscal years.

4.1. Creating a Fiscal Year

If you want to create a fiscal year in Power Query M, you need to know when your fiscal year starts and ends, which varies by company. We can again make use of the FiscalYearEnd parameter that indicates the month that the year ends.

Creating the Fiscal Year Number

With the FiscalYearEnd month defined, you can create the fiscal year number using this formula:

Date.Year( Date.AddMonths( [Date], 12 - FiscalYearEnd ) )

For example, applying this to July 1, 2025, and August 1, 2025, looks like this:

let
  FiscalYearEnd = 7, 

  // Output: 2025
  FiscalYear = Date.Year( Date.AddMonths( #date( 2025, 7, 1), 12 - FiscalYearEnd ) )

  // Output: 2026
  FiscalYear = Date.Year( Date.AddMonths( #date( 2025, 8, 1), 12 - FiscalYearEnd ) )
in
  FiscalYear

Since July is the last month of the fiscal year, the fiscal year for July 2025 is 2025, while August moves into the next fiscal year, 2026.

Differentiating Between Calendar and Fiscal Years

It is often useful to distinguish between a regular year and a fiscal year. After all, when moving both fields into a visual, how would a user know which one is the fiscal year and which is the calendar year?

To distinguish between calendar years and fiscal years, you can add a prefix to the fiscal year:

let
  FiscalYearEnd = 7, 

  // Output: 2025
  FiscalYear = Date.Year( Date.AddMonths( #date( 2025, 7, 1), 12 - FiscalYearEnd ) ),

  // Output: "FY2025"
  FiscalYearLabel = "FY" & Text.From( FiscalYear )
in
  FiscalYearLabel

4.2. Fiscal Start and End of Year

Other useful fields for fiscal calendars include the fiscal start of year and fiscal end of year dates. These are helpful for identifying the edges of your fiscal periods and are also useful for your DAX calculations.

Here’s how to create the Fiscal End of Year Date:

Date.EndOfMonth( Date.AddMonths( [Date], 12 - [FiscalMonth] ) )

This approach shifts the current date to the end of the month b using Date.EndOfMonth and then adjusts it by an offset to align with the fiscal year. For instance:

let
  myDate = #date( 2025, 8, 30 ),
  FiscalYearEnd = 7,
  Offset = if FiscalYearEnd = 12 then 0 else FiscalYearEnd - 12,


  // Output: July 31, 2026
  FiscalEndOfYear = 
  Date.EndOfMonth( Date.AddMonths( myDate , 12 - [FiscalMonth] ) )

  // Output: August 1, 2025  
  FiscalStartOfYear = 
    Date.AddYears( [FiscalEndOfYear], -1 ) + #duration( 1, 0, 0, 0)
in
  FiscalEndOfYear 

In this example the fiscal year ending is July. When passing the date August 30, 2025 to the logic this expressions returns a Fiscal Start of Year of August 1 2025, while the end of year is July 31 2026.

4.3. Fiscal Year Offset

To simplify your calculations in DAX, having a year offset column that shows the relative position of the fiscal year of the current row compared to today can be useful. The offset would be 0 for the current fiscal year, -2 for the one 2 years ago, and 5 for the fiscal year 5 years in the future. Here’s how you can compute it:

let
  FiscalYearEnd = 7,
  CurrentFiscalYear = Date.Year( Date.AddMonths( Today, 12 - FiscalYearEnd ) ),
  FiscalYearOffset = [FiscalYear] - CurrentFiscalYear)
in
  FiscalYearOffset

When we put these columns into a table and apply them to the start dates of each month in 2025, the result is as shown in the image below:

Okay, so now that we’ve figured out how to create a Fiscal Year and related year calculations, let’s move on to creating the Fiscal Month related calculations.

5. Fiscal Quarter Calculations

The fiscal quarter is another important element of the fiscal calendar. It help you to easily compare how the business performs between different periods.

5.1. Fiscal Quarter Number

To create a Fiscal Quarter, you will use the Fiscal Month we defined in the previous step. All we have to do is divide the Fiscal Month by 3 and round up the result.

Use this formula to determine the fiscal quarter:

Number.RoundUp( [Fiscal Month] / 3 ) 

Let’s look at two examples here:

let
  FQMonth10 = Number.RoundUp( 10 / 3 ), // Output: 4
  FQMonth11 = Number.RoundUp( 11 / 3 )  // Output: 4
in
  FQMonth11

If the fiscal month is 10, dividing by 3 gives approximately 3.33. Rounding up results in 4, placing the date in fiscal quarter 4. Similarly, for a fiscal month of 11, dividing by 3 gives approximately 3.67. Rounding up still results in 4, so this date is also in fiscal quarter 4.

5.2. Fiscal Month Of Quarter

The fiscal month of quarter indicates whether a month is the first, second, or third within a quarter. You can calculate it using the fiscal month number together with the fiscal quarter number.

The easiest way to compute the fiscal month of quarter is:

[FiscalMonthNumber] - 3 * ( [FiscalQuarterNumber] - 1 )

For example, fiscal month 5 in fiscal quarter 2 is the second month of that quarter:

5 - 3 * ( 2 - 1 ) // Output: 2

Now that we know how to create a Fiscal Quarter and determine the Fiscal Month of the Quarter, let’s move on to figuring out the starting and ending dates of a Fiscal Year.

5.3. Fiscal Quarter Start and End Date

In fiscal calendars, it’s useful to add the fiscal start and end dates of each quarter. These help users understand the time periods more clearly. We will use the FiscalMonthOfQuarter field to make these calculations.

With fiscal calendars, it’s not always clear for users what the start and end of the quarter is. It is therefore common to add a Fiscal Start of Quarter Date and Fiscal End of Quarter Date. We will use the outcome of the FiscalMonthOfQuarter field to make these calculations.

To calculate the Fiscal Start of Quarter date you can use:

// Returns the Fiscal Start of Quarter Date
Date.StartOfMonth( Date.AddMonths( [Date] , 1 - [FiscalMonthOfQuarter] ) )

To return the Fiscal End of Quarter date you can use the following formula:

// Returns the Fiscal End of Quarter Date
Date.EndOfMonth( Date.AddMonths( [Date] , 3 - [FiscalMonthOfQuarter] ) )

These periods depend on your fiscal year-end month. Here’s what these fields look like when applying them to a date table with all start of month dates in 2025:

These values are useful by themselves, but sometimes you may want to be more explicit about what you’re looking at.

5.4. Composite Fiscal Quarter Values

To avoid confusion with regular quarter numbers, it’s helpful to explicitly indicate that we are talking about fiscal quarters. We can add labels for the fiscal quarter, fiscal year, and the start and end dates of the fiscal quarter.

Here’s what that can look like:

Here the abbreviation FQ is used for Fiscal Quarter, while the label FY represents the Fiscal Year. So how do we compute these values?

Fiscal Quarter Label

To create a fiscal quarter label you can use:

// Returns the FiscalQuarterLabel
"FQ" & Text.From( [FiscalQuarterNumber] )

When running into fiscal quarter number 3, this expression returns Q3.

// Output is the Fiscal Quarter Label: "Q3"
"Q" & Text.From( 3 )

Fiscal Year Quarter Label

To combine the fiscal year and quarter you can use:

// Returns the FiscalYearQuarterLabel
"FY " & Text.From( [FiscalYearNumber] ) & " Q" & Text.From( [FiscalQuarterNumber] )

Here a value within Fiscal Quarter 3 of Fiscal year 2025 results in FY2025 Q3:

// Output is the Fiscal Year Quarter Label: "FY2025 Q3"
"FY " & Text.From( 2025 ) & " Q" & Text.From( 3 )

Fiscal Quarter Month label

To include the start and end dates of the fiscal quarter you can create a fiscal quartermonth label:

// Returns the FiscalQuarterMonthLabel
 "FQ" & Text.From([FiscalQuarter]) & ": " 
  & Date.ToText( [FiscalStartOfQuarter], "MMM yyyy" ) & " - " 
  & Date.ToText( [FiscalEndOfQuarter], "MMM yyyy" )

The following expression returns FQ3: Feb 2025 - Apr 2025 for any date value that lies between February and April 2025:

// Output: "FQ3: Feb 2025 - Apr 2025
 "FQ" & Text.From( 3 ) & ": " 
  & Date.ToText( #date( 2025, 2, 1), "MMM yyyy" ) & " - " 
  & Date.ToText( #date( 2025, 4, 30), "MMM yyyy" )

After all, the table conveys much clearer what periods we’re talking about compared to the table on the right, wouldn’t you agree?

5.5. Fiscal Day Indicators

It’s sometimes useful to know which day of a period we’re looking at. Especially since a fiscal calendar can start on a custom date. Here’s how you can do that.

You can create a fiscal day of year and fiscal day of quarter number by using:

let
  FiscalDayOfYear = Number.From( [Date] - [FiscalStartOfYear] ) + 1,
  FiscalDayOfQuarter = Number.From( [Date] - [FiscalStartOfQuarter] ) + 1
in
  FiscalDayOfQuarter

Here we find the different between the current date and the fiscal start of the year and quarter respectively. Since we’re looking at the day number of the year, you should add +1 to the formula.

Next to the day of a period, it can also be useful to know the total number of days in a period. Here’s how you can return the total number of days in a fiscal year and fiscal quarter:

let
  FiscalDaysInYear = Number.From( [FiscalEndOfYear] - [FiscalStartOfYear] ) + 1,
  FiscalDaysInQuarter = Number.From( [FiscalEndOfQuarter] - [FiscalStartOfQuarter] ) + 1
in
  FiscalDaysInQuarter 

Here we find the difference between the start and end of each period, and add one to the result to find the total number of days in the period.

5.6. Fiscal Quarter Offset

Finally, for easier time intelligence, you may want to create a Fiscal Quarter Offset number. You can do that as follows:

let
  CurrentFiscalMonth = Date.Month( Date.AddMonths( Today, - FiscalYearEnd ) ),
  CurrentFiscalQuarter = Number.RoundUp( CurrentFiscalMonth / 3 ),
  FiscalQuarterOffset = 
    (([FiscalYear] - CurrentFiscalYear) * 4) + ([FiscalQuarter] - CurrentFiscalQuarter)
in
  FiscalQuarterOffset

6. Download

To download the code that contains all columns relevant for fiscale calendars, you can use the following script:

let
    // The FiscalYearEnd determines in which month the fiscal year ends. If necessary you can adjust it. 
    FiscalYearEnd = 3,
    Today = Date.From(DateTime.LocalNow()),
    // Sets the start date for your calendar
    StartDate = #date(2023, 1, 1),
    // The calendar runs until the end of the current year. Change this if you need your calendar to run to another date. 
    EndDate = Date.EndOfYear(Today),
    // The current Fiscal Year value is a helper value for other calculations
    CurrentFiscalYear = Date.Year( Date.AddMonths( Today, 12 - FiscalYearEnd ) ),
    ListOfDates = List.Dates(StartDate, Duration.Days(EndDate - StartDate) + 1, #duration(1, 0, 0, 0)),
    MyDateTable = Table.FromList(ListOfDates, Splitter.SplitByNothing(), type table[Date = date], null, ExtraValues.Error),
    Add_FiscalYearNumber = Table.AddColumn(MyDateTable, "FiscalYear", each Date.Year( Date.AddMonths([ Date], 12 - FiscalYearEnd ) ), Int64.Type),  
    Add_FiscalYearLabel = Table.AddColumn(Add_FiscalYearNumber, "FiscalYearLabel", each "FY" & Text.From([FiscalYear]), Text.Type),
    // Allows you to select the 'Current' or 'Previous' year dynamically. For further reading, refer to: https://gorilla.bi/power-bi/set-default-slicer-value/
    Add_FiscalYearDefault = Table.AddColumn(Add_FiscalYearLabel, "FiscalYearDefault", each if [FiscalYear] = CurrentFiscalYear then "Current" else if [FiscalYear] = (CurrentFiscalYear -1 ) then "Previous" else "FY" & Text.From( [FiscalYear] ), Text.Type), 
    Add_FiscalStartOfYear = Table.AddColumn(Add_FiscalYearDefault, "FiscalStartOfYear", each [
  Offset = if FiscalYearEnd = 12 then 0 else FiscalYearEnd - 12,
  FiscalStartOfYear = Date.AddMonths( Date.StartOfYear( [Date] ), Offset )
][FiscalStartOfYear], Date.Type),  
    Add_FiscalEndOfYear = Table.AddColumn(Add_FiscalStartOfYear, "FiscalEndOfYear", each Date.AddMonths(Date.EndOfYear([Date]), FiscalYearEnd - 12), Date.Type),
// A year Offset value indicates the relative position of the current year (based on today's date) compared to the year in the current row. Returns 0 for the current year, -1 for the previous.
    Add_FiscalYearOffset = Table.AddColumn(Add_FiscalEndOfYear, "FiscalYearOffset", each [ 
  FiscalQuarterOffset = (([FiscalYear] - CurrentFiscalYear) )
][FiscalQuarterOffset], Int64.Type ), 
    Add_FiscalMonthNumber = Table.AddColumn(Add_FiscalYearOffset, "FiscalMonth", each Date.Month( Date.AddMonths([Date], - FiscalYearEnd ) ), Int64.Type),  
    // Allows you to select the 'Current' or 'Previous' month dynamically. For further reading, refer to: https://gorilla.bi/power-bi/set-default-slicer-value/
    Add_FiscalMonthDefault = Table.AddColumn(Add_FiscalMonthNumber, "FiscalMonthDefault", each if Date.IsInCurrentMonth([Date]) then "Current" else if Date.IsInPreviousMonth([Date]) then "Previous" else Text.From( [FiscalMonth] ), Text.Type),
    Add_FiscalQuarterNumber = Table.AddColumn(Add_FiscalMonthDefault, "FiscalQuarter", each Number.RoundUp( [FiscalMonth] / 3), Int64.Type), 
    Add_FiscalQuarterLabel = Table.AddColumn(Add_FiscalQuarterNumber, "FiscalQuarterLabel", each "FQ" & Text.From([FiscalQuarter]), Text.Type),
    Add_FiscalMonthOfQuarter = Table.AddColumn(Add_FiscalQuarterLabel, "FiscalMonthOfQuarter", each 
[FiscalMonth] - 3 * ([FiscalQuarter] - 1) , Int64.Type ),
    Add_FiscalYearQuarterLabel = Table.AddColumn(Add_FiscalMonthOfQuarter, "FiscalYearQuarterLabel", each "FY" & Text.From( [FiscalYear] ) & " Q" & Text.From( [FiscalQuarter] ), Text.Type),  
    Add_FiscalStartOfQuarter = Table.AddColumn(Add_FiscalYearQuarterLabel, "FiscalStartOfQuarter", each 
Date.StartOfMonth( Date.AddMonths( [Date] , 1-[FiscalMonthOfQuarter] )), Date.Type), 
    Add_FiscalEndOfQuarter = Table.AddColumn(Add_FiscalStartOfQuarter, "FiscalEndOfQuarter", each 
Date.EndOfMonth( Date.AddMonths( [Date] , 3-[FiscalMonthOfQuarter] )), Date.Type),
// A year Offset value indicates the relative position of the current quarter (based on today's date) compared to the quarter in the current row. Returns 0 for the current quarter, -1 for the previous.
    Add_FiscalQuarterOffset = Table.AddColumn(Add_FiscalEndOfQuarter, "FiscalQuarterOffset", each [ 
  CurrentFiscalMonth = Date.Month( Date.AddMonths( Today, - FiscalYearEnd ) ),
  CurrentFiscalQuarter = Number.RoundUp(CurrentFiscalMonth / 3),
  FiscalQuarterOffset = (([FiscalYear] - CurrentFiscalYear) * 4) + ([FiscalQuarter] - CurrentFiscalQuarter)
][FiscalQuarterOffset], Int64.Type ),
    Add_FiscalQuarterMonthLabel = Table.AddColumn(Add_FiscalQuarterOffset, "FiscalQuarterMonthLabel", each "FQ" & Text.From( [FiscalQuarter] ) & ": " & Date.ToText( [FiscalStartOfQuarter], "MMM yyyy" ) & " - " & Date.ToText( [FiscalEndOfQuarter], "MMM yyyy" ), type text ),
    Add_FiscalDayOfQuarter = Table.AddColumn(Add_FiscalQuarterMonthLabel, "FiscalDayOfQuarter", each Number.From( [Date] - 
[FiscalStartOfQuarter] ) + 1, Int64.Type ),
  // Renames all columns so they have spaces between words. Also removes underscores.
    #"Rename Columns" = Table.TransformColumnNames(Add_FiscalDayOfQuarter, each [
  SplitTextByTransition =  Splitter.SplitTextByCharacterTransition({"a".."z"}, {"A".."Z"})(_), 
  CombineValues = Text.Combine( SplitTextByTransition, " " ), 
  RemoveUnderscores = Text.Replace( CombineValues, "_", " ") ][RemoveUnderscores] )
in
    #"Rename Columns"

7. 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. And with the help of the columns we talked about in this article, you should have just what you need.

8. Frequently Asked Questions