Hello @putturps,
You can create a “Date Table” by loading the given code in the Advanced Editor. This code has been provided by the Enterprise DNA in their Resources.
let fnDateTable = (StartDate as date, EndDate as date, FYStartMonth as number) as table =>
let
DayCount = Duration.Days(Duration.From(EndDate - StartDate)),
Source = List.Dates(StartDate,DayCount,#duration(1,0,0,0)),
TableFromList = Table.FromList(Source, Splitter.SplitByNothing()),
ChangedType = Table.TransformColumnTypes(TableFromList,{{"Column1", type date}}),
RenamedColumns = Table.RenameColumns(ChangedType,{{"Column1", "Date"}}),
InsertYear = Table.AddColumn(RenamedColumns, "Year", each Date.Year([Date]),type text),
InsertYearNumber = Table.AddColumn(RenamedColumns, "YearNumber", each Date.Year([Date])),
InsertQuarter = Table.AddColumn(InsertYear, "QuarterOfYear", each Date.QuarterOfYear([Date])),
InsertMonth = Table.AddColumn(InsertQuarter, "MonthOfYear", each Date.Month([Date]), type text),
InsertDay = Table.AddColumn(InsertMonth, "DayOfMonth", each Date.Day([Date])),
InsertDayInt = Table.AddColumn(InsertDay, "DateInt", each [Year] * 10000 + [MonthOfYear] * 100 + [DayOfMonth]),
InsertMonthName = Table.AddColumn(InsertDayInt, "MonthName", each Date.ToText([Date], "MMMM"), type text),
InsertCalendarMonth = Table.AddColumn(InsertMonthName, "MonthInCalendar", each (try(Text.Range([MonthName],0,3)) otherwise [MonthName]) & " " & Number.ToText([Year])),
InsertCalendarQtr = Table.AddColumn(InsertCalendarMonth, "QuarterInCalendar", each "Q" & Number.ToText([QuarterOfYear]) & " " & Number.ToText([Year])),
InsertDayWeek = Table.AddColumn(InsertCalendarQtr, "DayInWeek", each Date.DayOfWeek([Date])),
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, "Week Number", each Date.WeekOfYear([Date])),
InsertMonthnYear = Table.AddColumn(InsertWeekNumber,"MonthnYear", each [Year] * 10000 + [MonthOfYear] * 100),
InsertQuarternYear = Table.AddColumn(InsertMonthnYear,"QuarternYear", each [Year] * 10000 + [QuarterOfYear] * 100),
ChangedType1 = Table.TransformColumnTypes(InsertQuarternYear,{{"QuarternYear", Int64.Type},{"Week Number", Int64.Type},{"Year", type text},{"MonthnYear", Int64.Type}, {"DateInt", Int64.Type}, {"DayOfMonth", Int64.Type}, {"MonthOfYear", Int64.Type}, {"QuarterOfYear", Int64.Type}, {"MonthInCalendar", type text}, {"QuarterInCalendar", type text}, {"DayInWeek", Int64.Type}}),
InsertShortYear = Table.AddColumn(ChangedType1, "ShortYear", each Text.End(Text.From([Year]), 2), type text),
AddFY = Table.AddColumn(InsertShortYear, "FY", each "FY"&(if [MonthOfYear]>=FYStartMonth then Text.From(Number.From([ShortYear])+1) else [ShortYear]))
in
AddFY
in
fnDateTable
After loading the “Date Table” in your data model, you can create a calculated column by writing the following DAX which will ultimately help you to categorize your Quarters as per the Financial Year. In this case, the Financial starts from Nov month. So it will be as follows -
FY Quarters =
SWITCH( TRUE() ,
Dates[MonthOfYear] = 11 , "Q1" ,
Dates[MonthOfYear] = 12 , "Q1" ,
Dates[MonthOfYear] = 1 , "Q1" ,
Dates[MonthOfYear] = 2 , "Q2" ,
Dates[MonthOfYear] = 3 , "Q2" ,
Dates[MonthOfYear] = 4 , "Q2" ,
Dates[MonthOfYear] = 5 , "Q3" ,
Dates[MonthOfYear] = 6 , "Q3" ,
Dates[MonthOfYear] = 7 , "Q3" ,
Dates[MonthOfYear] = 8 , "Q4" ,
Dates[MonthOfYear] = 9 , "Q4" ,
Dates[MonthOfYear] = 10 , "Q4" ,
BLANK() )
After this you will be able to analyze the numbers as per the Fiscal Year Quarter.
Have also attached the Date and PBIX file the reference alongwith the snapshot of the working.
Hoping this solution will meet your requirements.
Thanks and Regards,
Harsh
Power BI Date Table code.txt (2.8 KB)
Creating Date Table Dimension using only Fiscal Quarter Data.pbix (1.0 MB)
.