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"
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:
How do you handle the calculations for same period last year if you want to compare for example, Fiscal Week 201701 vs 201601? Same for if you want to look at last 2 weeks, last 4 weeks vs prior year? The SAMEPERIODLASTYEAR calculation doesn't add up correctly.
Thank you for posting this!
- dkay84_PowerBI8 years ago
Microsoft Employee
You will need to do some math/logic such as CALCULATE([Measure],Filter('Calendar','Calendar'[FiscalWeek]=...))
where the "..." is your offset. For example, if your fiscal week is 201812, and you want to see same week last year, then you must subtract 100 = 201712.
There may be other ways to do this, possibly even out of the box functions too.