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 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!
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:
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!