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.
Good evening everyone,
SundarRaj Thank you for your solution, which I have just looked at. The proposal is interesting, but as mentioned by jaineshp , I am starting from Power query Editor and not from an existing table in Excel.
jaineshp Thank you for summarising the steps clearly.
That is indeed the approach to take. The same as pa4cal, but removing the "Weeks Nr" column, which is no longer needed.
I will therefore use his solution
p45cal , that's perfect, thank you very much.
I appreciated the approach you suggested.
Here is the result with 3 full weeks + the last week up to the "end" date.
Thank you all.
Have a good evening.
Translated with DeepL.com (free version)
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.