Dynamic Calendar Week - How to resolve Wk53

[Anonymous]

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 whatever date I put into this parameter as the first week of the year and calculate the Week of Year for each date, returning to 1 when it gets to the next year. 

I've seen some solutions before that have struggled when it comes to Wk53 - they've either split this between Wk1 and Wk53 or they've made Wk53 longer than 7 days. Ideally I'd like to avoid having Wk53 if possible.

Any ideas on how I can do this?

Thanks,

MarkJames

[jdbuchanan71]

What are you wanting to do with the days that would fall into week 53?  If you want them in week 52 the you can just use the day of the year and if it is > 358 set the week to 52.

[Anonymous]

@jdbuchanan71  this wouldn't really work for leap years, like next year.

[jdbuchanan71]

So what are you wanting to do with the days that are in week 53?

[Anonymous]

@jdbuchanan71 i was thinking they could either fall into Wk52 or Wk1. But I've just done some playing around with this in Excel and the leapyear totally threw out my calculations - it gave me Wk52 being 14 days long!

Nightmare!

[Anonymous · Accepted solution]

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!