Week 53 end of year calculations

[srehman]

Hi Team,
In Excel and power query i am struggling in weekly calculations showing week 53. 30 Monay and 31Tuesday Dec 2019 calculating as a week 53.
As we know all calculations within week 1 to week 52 per year.
There is not much help on internet so far specially on Power Query.

Somebody as a professional solution.
Thanks

 

[sandy666]

what do you want to achieve?

 

[srehman]

what do you want to achieve?

Calculations fluctuating due to week days like as i said monday and Tuesday calculated as a week 52

 

[sandy666]

you can filter week 53 then you will get 52 weeks for 2019

 

[kennypete]

You may need to be clearer on what you consider "fluctuating due to week days" because Week 53 is considered correct in most systems: ISO week date - Wikipedia

If all you want to do is prevent 2019-12-30 and 31's Monday and Tuesday for this 2019/20 cut-over year being Week 53 then you could use the ISOWEEKNUM function. That produces fewer Week 53s than the DATENUM function, which will even return a Week 54 for some dates (2000-12-31 and 2028-12-31, as examples). Note, however, ISOWEEKNUM will return Week 53 for some days in cut-overs of years 2020-21 and 2026-27 during this decade. ISOWEEKNUM will also return 1 for some dates in late December, e.g. 2025-12-29, which I'm guessing you may also not want.

If you can be clear on the rules you want to apply then creating a user-defined function or a formula and using that as your bespoke solution is an option. My guess is it will utilise a mix of DATENUM and ISODATENUM plus some bespoke stuff to force things you don't want to see - e.g. Week 53 and (I guess) Week 1 starting on a day in December. Something like this perhaps? (where A2 is a date)

=IF(WEEKNUM(A2)>=52,IF(ISOWEEKNUM(A2)=1,52,IF(ISOWEEKNUM(A2)>52,52,ISOWEEKNUM(A2))),MIN(WEEKNUM(A2),ISOWEEKNUM(A2),ISOWEEKNUM(A2+1)))

I tried this out for the period 2019-2049: the downside is this makes for some very long Week 1 and Week 52 weeks, but it does prevent any Week 53s/54s, and ensures 1 January is always the start of Week 1. It also handles some weird days such as 2021-01-03 which is WEEKNUM Week 2 but ISOWEEKNUM Week 53. Some examples:

 

[srehman]

Thanks for detail answer

 

[pjmorris]

Not sure of the intended output, but one solution might be to use a custom calendar in PowerPivot - might be worth investigating.