Forum Discussion
Dynamic Calendar Week - How to resolve Wk53
- Anonymous7 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!
jdbuchanan71 this wouldn't really work for leap years, like next year.
So what are you wanting to do with the days that are in week 53?
- Anonymous7 years agoNot applicable
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!
- Anonymous7 years agoNot applicable
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!