Generate Calendar Table in Power BI with Fiscal Year Attributes

In Power BI go to
Get Data --> Blank Query
and paste into the Function windows the text below.

This function takes as parameters:
- StartYear (e.g., 2012)
- EndYear (e.g., 2018)
-FiscalYearStartMonth (e.g., 4)

And it will generate a calendar table for dates from Jan-1-<StartYear> till Dec-31-<EndYear> and columns for Fiscal Year, Fiscal Month, Fiscal Quarter, Fiscal Year Day where the Fiscal year begins on FiscalYearStartMonth

= (StartYear as number, EndYear as number, FiscalYearStartMonth as number)=>

let

    //Capture the date range from the parameters

    StartDate = #date(StartYear, 1, 1),

    EndDate = #date(EndYear, 12, 31),

//Get the number of dates that will be required for the table

    GetDateCount = Duration.Days(EndDate - StartDate)+1,

//Take the count of dates and turn it into a list of dates

    GetDateList = List.Dates(StartDate, GetDateCount,

    #duration(1,0,0,0)),

//Convert the list into a table

    DateListToTable = Table.TransformColumnTypes(Table.FromList(GetDateList, Splitter.SplitByNothing(), {"Date"}, null, ExtraValues.Error),{{"Date", type date}}),

//Create various date attributes from the date column

    //Add Year Column

    YearNumber = Table.AddColumn(DateListToTable, "Year",

    each Date.Year([Date]), Int64.Type),

//Add Quarter Column

    QuarterNumber = Table.AddColumn(YearNumber , "Quarter",

    each "Q" & Number.ToText(Date.QuarterOfYear([Date]))),

//Add Week Number Column

    WeekNumber= Table.AddColumn(QuarterNumber , "Week",

    each Date.WeekOfYear([Date]), Int64.Type),

//Add Month Number Column

    MonthNumber = Table.AddColumn(WeekNumber, "Month",

    each Date.Month([Date]), Int64.Type),

//Add Month Name Column

    MonthName = Table.AddColumn(MonthNumber , "Month Name",

    each Date.ToText([Date],"MMMM")),

//Add Year Day Number Column

    YearDayNumber= Table.AddColumn(MonthName , "Year Day",

    each Date.DayOfYear([Date]), Int64.Type),

//Add Week Day Number Column

    WeekDayNumber= Table.AddColumn(YearDayNumber , "Week Day",

    each Date.DayOfWeek([Date]), Int64.Type),

//Add Is Week Day Number Column

    IsWeekDay= Table.AddColumn(WeekDayNumber , "Is Weekday",

    each if (Date.DayOfWeek([Date])=0 or  Date.DayOfWeek([Date])=6) then 0 else 1, type number),

//Add Day of Week Column

    DayOfWeek = Table.AddColumn(IsWeekDay , "Day of Week",

    each Date.ToText([Date],"dddd")),

//Add Fiscal Month Column

    FiscalMonthNumber = Table.AddColumn(DayOfWeek , "Fiscal Month",

    each if [Month]>=FiscalYearStartMonth then [Month]-FiscalYearStartMonth+1 else [Month]-FiscalYearStartMonth+13, Int64.Type),

//Add Fiscal Quarter Column

    FiscalQuarterNumber = Table.AddColumn(FiscalMonthNumber , "Fiscal Quarter",

    each "FY Q" & Number.ToText(Number.RoundDown(([Fiscal Month]+2) / 3,0))),

//Add Fiscal Year Column

    Fiscalyear = Table.AddColumn(FiscalQuarterNumber , "Fiscal Year",

    each if [Month]>=FiscalYearStartMonth then [Year]+1 else [Year], Int64.Type),

//Add Fiscal Year Day Number Column

    FiscalYearDayNumber= Table.AddColumn(Fiscalyear , "Fiscal Year Day",

    each if [Month]>=FiscalYearStartMonth

    then ([Year Day] - Date.DayOfYear(#date([Year], FiscalYearStartMonth, 1)) + 1)

    else  if Date.IsLeapYear(#date([Year]-1,1,1))

          then 367 + [Year Day]- Date.DayOfYear(#date([Year]-1, FiscalYearStartMonth, 1))

    else 366 + [Year Day]- Date.DayOfYear(#date([Year]-1, FiscalYearStartMonth, 1))

   

    ,Int64.Type)

in    FiscalYearDayNumber