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.
- Create a ‘new query’, and copy the below code in there.
- Set the FiscalStartMonth to the month number in which your fiscal year starts
- 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.
- 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"