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.
I foresee a hiccup with the likes of #duration(26,0,0,0).
If you change the end date you will not necessarily get the full week that the pivot table implies.
Example: Change the end date to 15th Jan 2025 and you get this pivot:
where the data in the red rectangle implies a full week, but it's not:
The 18th and 19th Dec 2024 values are not included in the 30, it should be 36.
This is what the query looks like:
Perhaps use:
if (x) < Date.AddWeeks(Date.StartOfWeek(end, Day.Wednesday),-4) then
which will guarantee a full week's data is included
then the pivot becomes:
I note also that you wanted 4 weeks data at the end, not 5; changing -4 to -3 will put that right.
