Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Dynamic Calendar Week - How to resolve Wk53

Hi,   I'm using a data parameter to generate the first date of a calendar in Power Query that will run on until today.   I need to create a Week of Year field in Power Query that will take whatev...
  • Anonymous's avatar
    Anonymous
    7 years ago

    I figured out what I needed to do ... create a 4-5-4 calendar.

     

    I used the following link as a basis then adapted it for my calendar. Here's the M code I used:

     

    let
        Source = #"Enter a Calendar Start Date",
        Custom1 = List.Dates(Source, Number.From(DateTime.LocalNow())- Number.From(Source) ,#duration(1,0,0,0)),
        #"Converted to Table1" = Table.FromList(Custom1, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
        #"Renamed Columns" = Table.RenameColumns(#"Converted to Table1",{{"Column1", "Date"}}),
        #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"Date", type date}}),
        #"Inserted Day Name" = Table.AddColumn(#"Changed Type", "Day Name", each Date.DayOfWeekName([Date]), type text),
        #"Inserted Day of Week" = Table.AddColumn(#"Inserted Day Name", "DayOrder", each Date.DayOfWeek([Date],Day.Monday)+1, Int64.Type),
        #"Inserted Start of Week" = Table.AddColumn(#"Inserted Day of Week", "Start of Week", each Date.StartOfWeek([Date]), type date),
        #"Inserted End of Week" = Table.AddColumn(#"Inserted Start of Week", "End of Week", each Date.EndOfWeek([Start of Week]), type date),
        #"Added DD/MM" = Table.AddColumn(#"Inserted End of Week", "DD/WW", each Date.ToText([Start of Week],"dd/MM"),type text),
        #"Added Date ID" = Table.AddIndexColumn(#"Added DD/MM", "Date ID", 1, 1),
        #"Added Week ID" = Table.AddColumn(#"Added Date ID", "Week ID", each Number.RoundUp([Date ID]/7),Int64.Type),
        #"Added Week of Quarter" = Table.AddColumn(#"Added Week ID", "Week of Quarter", each [Week ID]-(Number.RoundUp([Week ID]/13)-1)*13,Int64.Type),
        #"Added Week of Year" = Table.AddColumn(#"Added Week of Quarter", "Week of Year", each [Week ID]-(Number.RoundUp([Week ID]/52)-1)*52,Int64.Type),
        #"Added Month ID" = Table.AddColumn(#"Added Week of Year", "Month ID", each if [Week of Quarter]>=1 and [Week of Quarter]<=4 then ((Number.RoundUp([Week ID]/13)-1)*3+1) else if [Week of Quarter]>=5 and [Week of Quarter]<=9 then ((Number.RoundUp([Week ID]/13)-1)*3+2) else if [Week of Quarter]>=10 and [Week of Quarter]<=13 then ((Number.RoundUp([Week ID]/13)-1)*3+3) else null,Int64.Type),
        #"Added FY Month" = Table.AddColumn(#"Added Month ID", "FY Month", each [Month ID]-(Number.RoundUp([Month ID]/12)-1)*12,Int64.Type),
        #"Added Quarter" = Table.AddColumn(#"Added FY Month", "Quarter", each if [FY Month] <= 3 then 1 else if [FY Month] <= 6 then 2 else if [FY Month] <= 9 then 3 else if [FY Month] <= 12 then 4 else 0,Int64.Type),
        #"Added Year ID" = Table.AddColumn(#"Added Quarter", "Year ID", each Number.RoundUp([Date ID]/364),Int64.Type),
        #"Added Year" = Table.AddColumn(#"Added Year ID", "Year", each if Date.Month(Date.EndOfWeek(#"Enter a Calendar Start Date")) <= 6 then (Date.Year(#"Enter a Calendar Start Date")+[Year ID])-1 else Date.Year(#"Enter a Calendar Start Date")+[Year ID],Int64.Type),
        #"Added Month Name" = Table.AddColumn(#"Added Year", "Month Name", each if ([FY Month] + Date.Month(Date.EndOfWeek(#"Enter a Calendar Start Date")))-1 <=12 then Date.ToText(#date([Year],([FY Month] + Date.Month(Date.EndOfWeek(#"Enter a Calendar Start Date")))-1,1),"MMMM") else Date.ToText(#date([Year],(([FY Month] + Date.Month(Date.EndOfWeek(#"Enter a Calendar Start Date")))-1)-12,1),"MMMM"),type text)
    in
        #"Added Month Name"

    I then added in a couple of additional Calculated Columns to get a MM-YYYY column. Here are the Measures for the columns:

     

    Calendar Year = CALCULATE(year(max('Calendar'[Date])),ALLEXCEPT('Calendar','Calendar'[Month ID]))
    MM/YY = left('Calendar'[Month Name],3) & "-" & RIGHT('Calendar'[Calendar Year],2)

     

    I did this so that if my Calendar Year begins on the last Monday in December it will bring this date into the following month.

     

    Phew!