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"
