Home > Blog > How to Create Calendar Tables in Power BI with Fiscal Year Selection

How to Create Calendar Tables in Power BI with Fiscal Year Selection

Creating calendar tables in Power BI is a game-changer for data analysis and reporting.

One common requirement when creating this type of date table is aligning the calendar with a fiscal year rather than a calendar year. In this blog post, we will walk through creating a calendar table in Power BI using Power Query that dynamically adjusts to your fiscal year. A calendar is an example of a "date table" in Power BI.

The table will include fiscal year, fiscal period, fiscal quarter, and additional related columns.

Why It's Important to Create a Calendar Table in Power BI

Knowing how to create a calendar table in Power BI is essential for several reasons:

1. Date Intelligence: Enables you to use time-based calculations such as year-to-date, quarter-to-date, month-over-month comparisons, and rolling averages.
2. Consistency: Provides a consistent date structure across all reports and analyses, ensuring all time-based data is aligned.
3. Performance: Improves performance by reducing the complexity of time-related calculations pre-calculated in the calendar table.
4. Flexibility: Allows for custom fiscal calendars, which is crucial for businesses that do not follow the standard calendar year.

Prerequisites for Creating a Calendar Table in Power BI

Before you dive into the Power Query code, you need to create two parameters:

1. StartOfFiscalYear determines the starting month of your fiscal year. By default, it is set to January 1, but you can tailor it to fit your company.
2. PriorYears: determines the number of historical years you should include in the date table. Set it to a value that meets your data analysis needs.

Power Query Code for Calendar Table

Here is the code you need to use to set up the calendar table:

// Create two parameters: StartOfFiscalYear and PriorYears before using the query. The StartOfFiscalYear parameter determines the start of year, by default, set the value to 1. The PriorYears parameter determines the number of historical years to include in the date table.


let
    // Calculate if the current date is within the fiscal year starting from StartOfFiscalYear
    checkDate = Date.From(DateTime.LocalNow()) >= #date(Date.Year(DateTime.LocalNow()), StartOfFiscalYear, 1),

    // Determine the start date of the data range based on the fiscal year and prior years
    StartDate = if checkDate 
                then #date(Date.Year(DateTime.LocalNow()) - PriorYears, StartOfFiscalYear, 1) 
                else #date(Date.Year(DateTime.LocalNow()) - PriorYears - 1, StartOfFiscalYear, 1),

    // Calculate the end date of the data range
    EndDate = Date.AddMonths(#date(Date.Year(DateTime.LocalNow()) + 1, StartOfFiscalYear, Date.Day(Date.EndOfMonth(StartDate))), -1),

    // Calculate the number of days in the date range
    NumberOfDays = Duration.Days(EndDate - StartDate),

    // Generate a list of dates covering the entire range
    Dates = List.Dates(StartDate, NumberOfDays + 1, #duration(1,0,0,0)),

    // Convert the list of dates into a table
    #"Converted to Table" = Table.FromList(Dates, Splitter.SplitByNothing()),

    // Rename the generated column to "Date"
    #"Renamed Columns" = Table.RenameColumns(#"Converted to Table", {{"Column1", "Date"}}),

    // Change the type of the "Date" column to date
    #"Changed Type of Date" = Table.TransformColumnTypes(#"Renamed Columns", {{"Date", type date}}),

    // Insert columns for Year, Month, and Month Name
    #"Inserted Year" = Table.AddColumn(#"Changed Type of Date", "Year", each Date.Year([Date]), type number),
    #"Inserted Month" = Table.AddColumn(#"Inserted Year", "Month", each Date.Month([Date]), type number),
    #"Inserted Month Name" = Table.AddColumn(#"Inserted Month", "Month Name", each Date.MonthName([Date]), type text),

    // Add columns for Fiscal Year, Fiscal Period, Fiscal Quarter
    #"Added Fiscal Year" = Table.AddColumn(#"Inserted Month Name", "Fiscal Year", each if StartOfFiscalYear = 1 then [Year] else if [Month] >= StartOfFiscalYear then [Year] + 1 else [Year], Int64.Type),
    #"Added Fiscal Period" = Table.AddColumn(#"Added Fiscal Year", "Fiscal Period", each if [Month] >= StartOfFiscalYear then [Month] - (StartOfFiscalYear - 1) else [Month] + (12 - StartOfFiscalYear + 1), Int64.Type),
    #"Added Fiscal Quarter" = Table.AddColumn(#"Added Fiscal Period", "Fiscal Quarter", each Number.RoundUp([Fiscal Period] / 3), Int64.Type),

    // Change the type of the Year column to text to facilitate formatting
    #"Changed Type of Year" = Table.TransformColumnTypes(#"Added Fiscal Quarter", {{"Year", type text}}),

    // Add columns for Short Month-Year, Fiscal Quarter Name, Short Month
    #"Added Short Month-Year" = Table.AddColumn(#"Changed Type of Year", "Short Month-Year", each Text.Start([Month Name], 3) & "-" & Text.End([Year], 2), type text),
    #"Added Fiscal Quarter Name" = Table.AddColumn(#"Added Short Month-Year", "Fiscal Quarter Name", each "Q" & Number.ToText([Fiscal Quarter]), type text),
    #"Added Short Month" = Table.AddColumn(#"Added Fiscal Quarter Name", "Short Month", each Text.Start([Month Name], 3), type text),

    // Add column to sort dates in a specific order based on Fiscal Year and Period
    #"Added SMY Order" = Table.AddColumn(#"Added Short Month", "SMY Order", each if Text.Length(Number.ToText([Fiscal Period])) = 1 then Number.ToText([Fiscal Year]) & "0" & Number.ToText([Fiscal Period]) else Number.ToText([Fiscal Year]) & Number.ToText([Fiscal Period]), type text)
    
in
    #"Added SMY Order"

How to Make a Calendar Table in Power BI

Follow these steps to complete the creation of your calendar table:

1. Create Parameters

• Open the Power Query Editor
• Go to the "Manage Parameters" dropdown and select "New Parameter."
• Create the StartOfFiscalYear parameter:
  • Name: StartOfFiscalYear
  • Type: Number
  • Current Value: 1
• Create the PriorYears parameter:
  • Name: PriorYears
  • Type: Number
  • Current Value: set this to the number of historical years you need

2. Enter the Code

• In the Power Query Editor, click on "New Source" and select "Blank Query"
• Go to the "Advanced Editor" and paste the code provided above

3. Apply and Close

• After pasting the code, click "Done."
• Finally, click "Close & Apply" to load the data into Power BI

Want to Learn More About the Transformative Capabilities of Power BI?

Get in touch with our team! Our experts can help you utilize Power BI to enhance your reporting abilities and utilize other aspects of the Power Platform to streamline work in your organization.

Under the terms of this license, you are authorized to share and redistribute the content across various mediums, subject to adherence to the specified conditions: you must provide proper attribution to Stoneridge as the original creator in a manner that does not imply their endorsement of your use, the material is to be utilized solely for non-commercial purposes, and alterations, modifications, or derivative works based on the original material are strictly prohibited.

Responsibility rests with the licensee to ensure that their use of the material does not violate any other rights.