Categories
Code

Power Query Ultimate Date Table for Power BI

You need a date table. It needs to give you day/week/month/quarter/half year visibility, and be able to adapt to whenever your fiscal year starts.

It’s an essential part of your ‘Star Schema’ which is how ideally you should organise your data in Power BI. I’ve written more about it here: Basic Power BI DAX Measures.

Anyway, creating a date table – using the below code – can be done in four easy steps:

  1. Create a ‘new query’, and copy the below code in there.
  2. Set the FiscalStartMonth to the month number in which your fiscal year starts
  3. Create two ‘parameter’ one called ‘StartYear’, ‘EndYear’ in which you
    fill in the 4-digit years you want to start & end your date calendar at.
  4. You now have a date table

Done!

For a more in-depth step-by-step article explaining Date Tables in Power BI, please read Create Date Table or Calendar in Power Query M by Rick de Groot of Gorilla BI.

By the end you should end up with the following elements in your Queries list (to read up on why you need the Unzipcontents check out How to ingest Excel or (zipped!) CSV files properly in Power Query for Power BI or Excel).

let
    FiscalStartMonth = 01, // Enter the regular month number on which your fiscal year starts. For instance April = 4, which will start the Fiscal Year in April
    FiscalEndMonth = if FiscalStartMonth = 1 then 12 else FiscalStartMonth-1,
    StartDate = #date(StartYear,FiscalStartMonth,1),
    EndDate = #date(EndYear,FiscalEndMonth,31),    
    FiscalStartWeek = Number.Round((FiscalStartMonth-1)*(52/12)+1),
    NumberOfDays = Duration.Days(EndDate-StartDate),
    Dates = List.Dates(StartDate,NumberOfDays+1,#duration(1,0,0,0)),
    #"Converted to Table" = Table.FromList(Dates, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Changed Type" = Table.TransformColumnTypes(#"Converted to Table",{{"Column1", type date}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Column1", "Date"}}),
    #"Inserted Week" = Table.AddColumn(#"Renamed Columns", "Fiscal Week Numb", 
each Number.ToText(
if Date.WeekOfYear([Date]) < FiscalStartWeek+1 then Date.WeekOfYear([Date]) + 52-FiscalStartWeek else Date.WeekOfYear([Date]) - FiscalStartWeek
, "00" )
),
    #"Inserted Month" = Table.AddColumn(#"Inserted Week", "Fiscal Month Numb", 
each Number.ToText(
 if Date.Month([Date]) < FiscalStartMonth then Date.Month([Date]) + -(FiscalStartMonth-1-12) else Date.Month([Date]) -(FiscalStartMonth-1)
, "00" )
),
    #"Inserted Quarter" = Table.AddColumn(#"Inserted Month", "Fiscal Quarter Numb", 
each 
Number.Round(Number.FromText([Fiscal Month Numb])/3+0.2, 0, RoundingMode.Up)
), 
    #"Inserted Half" = Table.AddColumn(#"Inserted Quarter", "Fiscal Half Numb", each 
if [Fiscal Quarter Numb] < 3 then 1 else 2
),
    #"Inserted Year" = Table.AddColumn(#"Inserted Half", "Fiscal Year Numb", each if FiscalStartMonth = 1 then Date.Year([Date]) -2000 else if Date.Month([Date]) < FiscalStartMonth then Date.Year([Date]) -2000 else Date.Year([Date]) -1999),
    #"Add Calendar Year" = Table.AddColumn(#"Inserted Year", "Calendar Year", each Date.Year([Date]), Int64.Type),
    #"Inserted Month Name" = Table.AddColumn(#"Add Calendar Year", "Month Name", each Date.MonthName([Date]), type text),
    #"Inserted Short Month" = Table.AddColumn(#"Inserted Month Name", "Month", each Text.Start([Month Name],3)),
    #"Inserted Fiscal Text" = Table.AddColumn(#"Inserted Short Month", "Fiscal Year", each Text.Combine({"FY", Text.From([Fiscal Year Numb], "en-US")}), type text),
    #"Added FYY-Q" = Table.AddColumn(#"Inserted Fiscal Text", "Fiscal Quarter", each Text.Combine({[Fiscal Year], "-Q", Text.From([Fiscal Quarter Numb], "en-US")}), type text),
    #"Added FYY-Q Sort" = Table.AddColumn(#"Added FYY-Q", "FYY-Q Sort", each [Fiscal Year Numb]*100 + [Fiscal Quarter Numb]),
    #"Added MMM-YY" = Table.AddColumn(#"Added FYY-Q Sort", "Fiscal Month", each [Fiscal Year] & "-" & Text.Start([Month Name],3)),
    #"Added MMM-YY Sort" = Table.AddColumn(#"Added MMM-YY", "FYY-MMM Sort", each [Fiscal Year Numb]*100 + Number.FromText([Fiscal Month Numb])),
    #"Added WW-YY" = Table.AddColumn(#"Added MMM-YY Sort", "Fiscal Week", each [Fiscal Year] & "-" & Text.From([Fiscal Week Numb])),
    #"Added WW-YY Sort" = Table.AddColumn(#"Added WW-YY", "FYY-WW Sort", each [Fiscal Year Numb]*100 + Number.FromText([Fiscal Week Numb])),
    #"Insert Q+numb" = Table.AddColumn(#"Added WW-YY Sort", "Fiscal Quarter Q", each Text.Combine({"Q", Text.From([Fiscal Quarter Numb], "en-IE")}), type text),
    #"Insert H+numb" = Table.AddColumn(#"Insert Q+numb", "Fiscal Half H", each Text.Combine({"H", Text.From([Fiscal Half Numb], "en-IE")}), type text)
in
    #"Insert H+numb"