undefined

wilsongregt · ‎09-02-2021

I'm attempting to create a custom fiscal calendar based on parameters. I need a distinct countifs on the weeks within a quarter and weeks withing a month (called Period) so I can use it for quarterly and monthly averages. Trying to avoid using If statethe numbers manualy because I'll have a 53w year eventualy.

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"}}), 
    #"Insert Day of the Week Description" = Table.AddColumn(RenamedColumns, "Day of the Week Description", each Date.ToText([Date], "dddd"), type text), 
    #"Insert Day of the Week Number" = Table.AddColumn(#"Insert Day of the Week Description", "Day of the Week Number", each Date.DayOfWeek([Date],0)), 
    #"Insert End of the Fiscal Week Date" = Table.AddColumn(#"Insert Day of the Week Number", "End of the Fiscal Week", each Date.EndOfWeek([Date],0), type date),
    #"Insert First Fiscal Week Ending Date" = Table.AddColumn(#"Insert End of the Fiscal Week Date", "First Fiscal Week Ending", each List.Min (#"Insert End of the Fiscal Week Date"[End of the Fiscal Week])),    
    DateOffset = Table.AddColumn(#"Insert First Fiscal Week Ending Date", "Offset", each [End of the Fiscal Week]-[First Fiscal Week Ending]),
    #"Changed Type" = Table.TransformColumnTypes(DateOffset,{{"Offset", Int64.Type}}),
    #"Insert Fiscal Week Number" = Table.AddColumn(#"Changed Type", "Fiscal Week Number", each [Offset]/7+1),
    #"Insert Fiscal Quarter" = Table.AddColumn(#"Insert Fiscal Week Number", "Fiscal Quarter", each if [Fiscal Week Number] <= 13 then 1 else if [Fiscal Week Number] >= 14 and [Fiscal Week Number] <= 26 then 2 else if [Fiscal Week Number] >= 27 and [Fiscal Week Number] <= 39 then 3 else 4),
    #"Insert Week of Fiscal Quarter" = Table.AddColumn(#"Insert Fiscal Quarter", "Week of Quarter", each if [Fiscal Week Number] <> 53 then ([Fiscal Week Number] - (Number.RoundUp([Fiscal Week Number]/13)-1) * 13) else 14),
    #"Insert the Period of the Quarter" = Table.AddColumn(#"Insert Week of Fiscal Quarter", "Period of the Quarter", each if [Week of Quarter] <= 4 then 1 else if [Week of Quarter] >= 5 and [Week of Quarter] <= 9 then 2 else 3),
    fnPeriod454a = (WeekNum) => let 
      Periods = { 
      {(x)=>x<5,  [Fiscal Period=1, Month Name="October"]}, 
      {(x)=>x<10, [Fiscal Period=2, Month Name="November"]}, 
      {(x)=>x<14, [Fiscal Period=3, Month Name="December"]}, 
      {(x)=>x<18, [Fiscal Period=4, Month Name="January"]}, 
      {(x)=>x<23, [Fiscal Period=5, Month Name="February"]}, 
      {(x)=>x<27, [Fiscal Period=6, Month Name="March"]}, 
      {(x)=>x<31, [Fiscal Period=7, Month Name="April"]}, 
      {(x)=>x<36, [Fiscal Period=8, Month Name="May"]}, 
      {(x)=>x<40, [Fiscal Period=9, Month Name="June"]}, 
      {(x)=>x<44, [Fiscal Period=10, Month Name="July"]}, 
      {(x)=>x<49, [Fiscal Period=11, Month Name="August"]}, 
      {(x)=>true, [Fiscal Period=12, Month Name="September"]} 
        }, 
      Result = List.First(List.Select(Periods, each _{0}(WeekNum))){1} 
    in 
      Result,

    InsertPeriod454 = Table.AddColumn(#"Insert the Period of the Quarter", "Period454Record", each fnPeriod454a([Fiscal Week Number])),
    #"Expanded Period454Record" = Table.ExpandRecordColumn(InsertPeriod454, "Period454Record", {"Fiscal Period", "Month Name"}, {"Fiscal Period", "Month Name"})
in
    #"Expanded Period454Record"