Forum Discussion
Custom Fiscal Year Calendar
- 9 years ago
For anyone who is interested, I was able to adapt the approach in this blog post to solve my calendar needs. Take a look if you're interested in the solution:
- 9 years ago
Here is the code from the Advanced Editor for those who prefer not to download the file (red text refers to paramters but can be replaced with hard coded dates or dynamic retrieval of earliest and latest dates in you Fact 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"}}), InsertDayName = Table.AddColumn(RenamedColumns, "DayOfWeekName", each Date.ToText([Date], "dddd"), type text), InsertDayWeek = Table.AddColumn(InsertDayName, "DayInWeek", each Date.DayOfWeek([Date],6)+1), InsertWeekEnding = Table.AddColumn(InsertDayWeek, "WeekEndingFriday", each Date.EndOfWeek([Date],6), type date), InsertCurrentSaturday = Table.AddColumn(InsertWeekEnding, "CurrentSaturday", each Date.AddDays([Date], -Date.DayOfWeek([Date],6)), type date), DateOffset = Table.AddColumn(InsertCurrentSaturday, "Offset", each Date.FromText(Number.ToText(Date.Year([CurrentSaturday])) & "-02-01") - [CurrentSaturday]), #"Changed Type" = Table.TransformColumnTypes(DateOffset,{{"Offset", Int64.Type}}), InsertISOWeekFeb1 = Table.AddColumn(#"Changed Type", "ISOWeekFeb1", each if [Offset] > 6 then Date.FromText(Number.ToText(Date.Year([CurrentSaturday])-1) & "-02-01") else Date.FromText(Number.ToText(Date.Year([CurrentSaturday])) & "-02-01"),type date), InsertISOWeekYear = Table.AddColumn(InsertISOWeekFeb1, "ISOWeekYear", each Date.Year([ISOWeekFeb1])), InsertISOWeekFirstSat = Table.AddColumn(InsertISOWeekYear, "ISOWeekFirstSat", each if [CurrentSaturday] < [ISOWeekFeb1] then Date.AddDays([CurrentSaturday],0) else Date.AddDays([ISOWeekFeb1], - Date.DayOfWeek([ISOWeekFeb1],6) ), type date), InsertFYWeekNum = Table.AddColumn(InsertISOWeekFirstSat, "ISOWeekNum", each Number.RoundUp(((Duration.Days(Duration.From([Date] - [ISOWeekFirstSat]))+1) /7 )), type number), FiscalYear = Table.AddColumn(InsertFYWeekNum, "FY", each [ISOWeekYear]+1), InsertFYWeekID = Table.AddColumn(FiscalYear, "ISOWeekID", each [FY] * 100 + [ISOWeekNum], type number), InsertIFYWeekNameLong = Table.AddColumn(InsertFYWeekID, "ISOWeekNameLong", each Text.From([FY]) & "-W" & Text.End( "0" & Text.From([ISOWeekNum]),2) & "-" & Date.ToText([Date],"ddd")), #"Renamed Columns" = Table.RenameColumns(InsertIFYWeekNameLong,{{"ISOWeekNameLong", "FYWeekNameLong"}, {"ISOWeekID", "FYWeekID"}, {"ISOWeekNum", "FYWeekNum"}, {"ISOWeekFirstSat", "FYWeekFirstSat"}}), #"FY Quarter" = Table.AddColumn(#"Renamed Columns", "FY Quarter", each if [FYWeekNum] <= 13 then 1 else if [FYWeekNum] >= 14 and [FYWeekNum] <= 26 then 2 else if [FYWeekNum] >= 27 and [FYWeekNum] <= 39 then 3 else 4), #"Week of FY Quarter" = Table.AddColumn(#"FY Quarter", "Week of Quarter", each if [FYWeekNum] <> 53 then ([FYWeekNum] - (Number.RoundUp([FYWeekNum]/13)-1) * 13) else 14), #"Quarter Week ID" = Table.AddColumn(#"Week of FY Quarter", "QtrWeekID", each [FY Quarter]*100+[Week of Quarter]), #"FY Quarter ID" = Table.AddColumn(#"Quarter Week ID", "FYQtrID", each [FY]*100+[FY Quarter]), #"Changed Type1" = Table.TransformColumnTypes(#"FY Quarter ID",{{"FY Quarter", Int64.Type}, {"Week of Quarter", Int64.Type}, {"QtrWeekID", Int64.Type}, {"FYQtrID", Int64.Type}}), fnPeriod454a = (weekNum) => let Periods = { {(x)=>x<5, [P=1, M="Feb"]}, {(x)=>x<10, [P=2, M="Mar"]}, {(x)=>x<14, [P=3, M="Apr"]}, {(x)=>x<18, [P=4, M="May"]}, {(x)=>x<23, [P=5, M="Jun"]}, {(x)=>x<27, [P=6, M="Jul"]}, {(x)=>x<31, [P=7, M="Aug"]}, {(x)=>x<36, [P=8, M="Sep"]}, {(x)=>x<40, [P=9, M="Oct"]}, {(x)=>x<44, [P=10, M="Nov"]}, {(x)=>x<49, [P=11, M="Dec"]}, {(x)=>true, [P=12, M="Jan"]} }, Result = List.First(List.Select(Periods, each _{0}(weekNum))){1} in Result, InsertPeriod454 = Table.AddColumn(#"Changed Type1", "Period454Record", each fnPeriod454a([FYWeekNum])), #"Expanded Period454Record" = Table.ExpandRecordColumn(InsertPeriod454, "Period454Record", {"M", "P"}, {"M", "P"}), #"Renamed Columns1" = Table.RenameColumns(#"Expanded Period454Record",{{"M", "FY Month Name"}, {"P", "FY Month ID"}}), #"Removed Columns" = Table.RemoveColumns(#"Renamed Columns1",{"CurrentSaturday", "Offset", "ISOWeekFeb1", "ISOWeekYear", "FYWeekFirstSat"}) in #"Removed Columns"
Were you aware that the latest Feb 2018 update (2.55.5010.521) allows a custom date table to be specified? It may be worth looking at this.
Chris
Thank you Chris, and yes, I did see this. My data is only listed at a fiscal week level, so the dynamic SAMEPERIODLASTYEAR calculations do not add up correctly. I'm new to Power BI, so it may be figuring out how to create with an OFFSET calc.