Power Query - Fiscal Calendar 445 Current Month Week

Question

I am trying to build a fiscal calendar in Power Query to provide this calendar as a dataflow for other departments too. In the past, I have used a DAX Calendar but this has its limits for sharing the calendar, right? So, I already have the Date, Fiscal Year and ISO/ Fiscal week information, using this M-Script:

let
    StartDate        = #date (2009,1,1),
    EndDate          = #date (2024,12,31),
    ListOfDates      = List.Dates(StartDate, DurationDays, #duration(1, 0, 0, 0)),
    DurationDays     = Duration.Days (EndDate - StartDate) + 1,
    TableOfDates     = Table.FromList(ListOfDates, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    DateColText      = Table.RenameColumns(TableOfDates,{{"Column1", "Date"}}),
    DateCol          = Table.TransformColumnTypes(DateColText,{{"Date", type date}}),
    WeekdayCol       = Table.AddColumn(DateCol, "Weekday Number", each Date.DayOfWeek([Date], Day.Monday) + 1,Int64.Type),
    #"Inserted Day Name" = Table.AddColumn(WeekdayCol, "Day Name", each Date.DayOfWeekName([Date]), type text),
    IsoWeekYearCol   = Table.AddColumn(#"Inserted Day Name", "Iso Week Year", each Date.Year(Date.AddDays([Date], 4-[Weekday Number])), Int64.Type),
    IsoWeekNrCol     = Table.AddColumn(IsoWeekYearCol, "Iso Week Nummer", each (Duration.Days(Date.AddDays([Date], 4-[Weekday Number]) - #date([Iso Week Year], 1 , 7 - Date.DayOfWeek(#date([Iso Week Year],1,4), Day.Monday)))/7)+1, Int64.Type),
    IsoWeekdayExtCol = Table.AddColumn(IsoWeekNrCol, "Iso Weekday Extended", each Text.From([Iso Week Year]) & "-W" & Text.End("0" &     Text.From([Iso Week Nummer]), 2) & "-"&  Text.From([Weekday Number]))
in
    IsoWeekdayExtCol

But how can I now add a column for the corresponding fiscal month and also a individual column for CurrentMonth and CurrentWeek.

How would you do that?

Is your final quarter also going to be 445? Or will it be 454? Does your fiscal year end on the last Saturday of the last month of the fiscal year? Or on the closest Saturday to the end of the last month of the fiscal year? What month is the last month of your fiscal year? — Ron Rosenfeld
Thank you so much for your support @RonRosenfeld! It is a 445 calendar (446 for 2020). The last month is December. 2020 end is 03.01.2020, 2021 end is 02.01.2022 — joshua

Ron Rosenfeld Ron Rosenfeld · Accepted Answer · 2021-02-07T21:59:45

To add a column with the corresponding fiscal month, you can derive it from the weeknumber (which you've already calculated). Since the months go 4-4-5, the associated month is predictable from the weeknumber.

   #"Added Custom" = Table.AddColumn(IsoWeekdayExtCol, "Fiscal Month", each 
             if List.Contains({1..4},[Iso Week Nummer]) then "January"
        else if List.Contains({5..8},[Iso Week Nummer]) then "February"
        else if List.Contains({9..13},[Iso Week Nummer]) then "March"
        else if List.Contains({14..17},[Iso Week Nummer]) then "April"
        else if List.Contains({18..21},[Iso Week Nummer]) then "May"
        else if List.Contains({22..26},[Iso Week Nummer]) then "June"
        else if List.Contains({27..30},[Iso Week Nummer]) then "July"
        else if List.Contains({31..34},[Iso Week Nummer]) then "August"
        else if List.Contains({35..39},[Iso Week Nummer]) then "September"
        else if List.Contains({40..43},[Iso Week Nummer]) then "October"
        else if List.Contains({44..47},[Iso Week Nummer]) then "November"
        else "December")

By CurrentMonth and CurrentWeek I am assuming you mean an entire column filled with today's fiscal month and today's fiscal week.

You just need to add a lookup type of column, where you determine the row to return based on today's date. So:

    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Current Month (fiscal)", each 
        Table.Column(#"Added Custom","Fiscal Month")
            {List.PositionOf(Table.Column(#"Added Custom","Date"),Date.From(DateTime.LocalNow()))}),

    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Current Week (fiscal)", each 
        Table.Column(#"Added Custom","Iso Week Nummer")
            {List.PositionOf(Table.Column(#"Added Custom","Date"),Date.From(DateTime.LocalNow()))})

Power Query - Fiscal Calendar 445 Current Month Week

1 Answers