When working with Power BI or other Power Query-supported tools, having a well-structured calendar table is essential for time-based analysis. In this blog post, we will walk through an M Language function that generates a comprehensive calendar table.

Why Use a Calendar Table?

A calendar table provides essential time-based fields such as year, quarter, month, day of the week, and fiscal year details. This helps in creating insightful reports and enables proper time intelligence calculations.

M Language Function for a Calendar Table

The following function generates a calendar table given a start date, an end date, and the starting month of the fiscal year.

let fnDateTable = (StartDate as date, EndDate as date, FYStartMonth as number) as table =>
  let
    DayCount = Duration.Days(Duration.From(EndDate - StartDate)) + 1,
    Source = List.Dates(StartDate, DayCount, #duration(1,0,0,0)),
    TableFromList = Table.FromList(Source, Splitter.SplitByNothing(), {"Date"}, [Type = type date]),   
  
    InsertYear = Table.AddColumn(TableFromList, "Year", each Date.Year([Date]), type number),
    InsertYearNumber = Table.AddColumn(InsertYear, "YearNumber", each Date.Year([Date]), type number),
    InsertQuarter = Table.AddColumn(InsertYearNumber, "QuarterOfYear", each Date.QuarterOfYear([Date]), type number),
    InsertMonth = Table.AddColumn(InsertQuarter, "MonthOfYear", each Date.Month([Date]), type number),
    InsertDay = Table.AddColumn(InsertMonth, "DayOfMonth", each Date.Day([Date]), type number),
    InsertDateInt = Table.AddColumn(InsertDay, "DateInt", each [Year] * 10000 + [MonthOfYear] * 100 + [DayOfMonth], type number),
    
    InsertMonthName = Table.AddColumn(InsertDateInt, "MonthName", each Date.ToText([Date], "MMMM"), type text),
    InsertCalendarMonth = Table.AddColumn(InsertMonthName, "MonthInCalendar", each Text.Start([MonthName], 3) & " " & Text.From([Year]), type text),
    InsertCalendarQtr = Table.AddColumn(InsertCalendarMonth, "QuarterInCalendar", each "Q" & Text.From([QuarterOfYear]) & " " & Text.From([Year]), type text),
    
    InsertDayWeek = Table.AddColumn(InsertCalendarQtr, "DayInWeek", each Date.DayOfWeek([Date]) + 1, type number),
    InsertDayName = Table.AddColumn(InsertDayWeek, "DayOfWeekName", each Date.ToText([Date], "dddd"), type text),
    InsertWeekEnding = Table.AddColumn(InsertDayName, "WeekEnding", each Date.EndOfWeek([Date]), type date),
    InsertWeekNumber = Table.AddColumn(InsertWeekEnding, "WeekNumber", each Date.WeekOfYear([Date]), type number),
    
    InsertMonthnYear = Table.AddColumn(InsertWeekNumber, "MonthnYear", each [Year] * 10000 + [MonthOfYear] * 100, type number),
    InsertQuarternYear = Table.AddColumn(InsertMonthnYear, "QuarternYear", each [Year] * 10000 + [QuarterOfYear] * 100, type number),
    InsertShortYear = Table.AddColumn(InsertQuarternYear, "ShortYear", each Number.ToText(Number.Mod([Year], 100)), type text),

    AddFY = Table.AddColumn(InsertShortYear, "FY", each "FY" & Text.From(if [MonthOfYear] >= FYStartMonth then [Year] + 1 else [Year]), type text)
  in
    AddFY

Explanation of the Function

• List.Dates generates a list of dates between the given StartDate and EndDate.
• Table.AddColumn adds multiple time-related columns like year, quarter, month, day, fiscal year, and week number.
• Date.Year, Date.Month, Date.Day extract the respective date components.
• Text.Start is used to extract the first three letters of the month name.
• Fiscal Year Calculation assigns the correct fiscal year based on the specified FYStartMonth.

How to Use the Function

1. Open Power BI Desktop and navigate to the Power Query Editor.
2. Click New Query > Blank Query.
3. Open the Advanced Editor and replace the code with the function above.
4. Invoke the function by providing StartDate, EndDate, and FYStartMonth.
5. Load the table into Power BI for further analysis.

Conclusion

This M function provides a robust calendar table for Power BI and Power Query applications. By utilizing it, you can enhance your reporting capabilities and efficiently handle time-based calculations.

Do you have any additional fields you’d like to include in the calendar table? Let us know in the comments!