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.

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 ‘StartDate’, ‘EndDate’ 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.

let
    FiscalStartMonth = 04, // 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 Date.Month([Date]) < FiscalStartMonth then Date.Year([Date]) -2000 else Date.Year([Date]) -1999),
    #"Inserted Month Name" = Table.AddColumn(#"Inserted 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"