Forum Discussion

dkay84_PowerBI's avatar
dkay84_PowerBI
Microsoft Employee
9 years ago
Solved

Custom Fiscal Year Calendar

This is not for the faint of heart but I'm hoping to crowdsource a solution as I work on it myself.  Here is the description of the calendar I need to model in PowerQuery/Query Editor:   The Compan...
  • dkay84_PowerBI's avatar
    dkay84_PowerBI
    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:

     

    Date Dimension PBIX

     

  • dkay84_PowerBI's avatar
    dkay84_PowerBI
    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"