Fiscal Calendar with Custom Start Month in Power Query M

Builds a calendar table where the fiscal year and fiscal month are calculated from a custom fiscal year start month instead of January.

Date & TimeIntermediatefiscalcalendardate tablecustom year

Power Query M code

let
    StartDate = #date(2020, 1, 1),
    EndDate = #date(2029, 12, 31),
    FiscalYearStartMonth = 4,
    DayCount = Duration.Days(EndDate - StartDate) + 1,
    Dates = List.Dates(StartDate, DayCount, #duration(1, 0, 0, 0)),
    ToTable = Table.FromList(Dates, Splitter.SplitByNothing(), {"Date"}),
    ChangedType = Table.TransformColumnTypes(ToTable, {{"Date", type date}}),
    AddedFiscalMonth = Table.AddColumn(ChangedType, "FiscalMonth", each Number.Mod(Date.Month([Date]) - FiscalYearStartMonth, 12) + 1, Int64.Type),
    AddedFiscalYear = Table.AddColumn(AddedFiscalMonth, "FiscalYear", each if Date.Month([Date]) >= FiscalYearStartMonth then Date.Year([Date]) + 1 else Date.Year([Date]), Int64.Type)
in
    AddedFiscalYear

Generate a date table in Power Query M

Notes

Set FiscalYearStartMonth to the month your fiscal year begins (4 = April). FiscalYear reports the year the fiscal period ends in.

Requirements

How to use this snippet

  1. In Power BI Desktop, choose Transform data to open Power Query, then open the Advanced Editor on a query (or start from a blank query).
  2. Paste the snippet and replace the placeholder names (tables, columns, file paths) with your own. Check the requirements above first.
  3. Run it through the Power Query M Formatter to tidy the layout.

Related Power Query M snippets

More Power Query M resources

Browse all 145 snippets in the Power Query M Code Snippets, or search the library for something similar to Fiscal Calendar with Custom Start Month.