Forum Discussion
Custom calendar
How about this as a pivot table presentation:
I stayed with Wednesday being the start of a week so that my results match your results.
Change your end date in cell 'end' to one within the first 2 or 3 weeks of the year to see what happens when you refresh the pivot at cell V13.
See the calendar query in the attached which outputs directly to the pivot.
Hope it gives you some ideas.
Edit: Note, that if you want the week to begin on a Monday, there are 2 places where you need to change that:
1. The first step called FirstDateOfWeekNos where you change Day.Wednesday to Day.Monday (or just delete Day.Wednesday and its preceding comma since Monday is the default if you don't supply that argument), and
2. The step called Inserted Start of Week where you do the same.
As the expected result you mentioned in file you shared
You can follow this appraoch
(I considered wednesday as week start day)
let
Source = List.Dates(#date(2023,1,1),1100,#duration(1,0,0,0)),
#"Converted to table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Changed column type" = Table.TransformColumnTypes(#"Converted to table", {{"Column1", type date}}),
#"Renamed columns" = Table.RenameColumns(#"Changed column type", {{"Column1", "Date"}}),
#"Added custom" = Table.TransformColumnTypes(Table.AddColumn(#"Renamed columns", "Year", each Date.Year([Date])), {{"Year", Int64.Type}}),
#"Added custom 1" = Table.TransformColumnTypes(Table.AddColumn(#"Added custom", "Month Nr", each Date.Month([Date])), {{"Month Nr", Int64.Type}}),
#"Added custom 2" = Table.TransformColumnTypes(Table.AddColumn(#"Added custom 1", "Month Name", each Date.MonthName([Date])), {{"Month Name", type text}}),
#"Added custom 3" = Table.TransformColumnTypes(Table.AddColumn(#"Added custom 2", "Week Nr", each if [Date] < Date.From((DateTime.LocalNow() - #duration(28,0,0,0))) then [Year] else Date.WeekOfYear([Date])), {{"Week Nr", Int64.Type}}),
#"Added custom 4" = Table.AddColumn(#"Added custom 3", "Range", each if [Date] < Date.From((DateTime.LocalNow() - #duration(28,0,0,0))) then "" else Text.From (Date.Day(Date.StartOfWeek([Date], Day.Wednesday))) & "-" & Text.From (Date.Day(Date.EndOfWeek([Date], Day.Wednesday))))
in
#"Added custom 4"Output:
Connect on LinkedIn
|