Week 53 Comparisons

[DanielleW1]

Hi all,

I wanted to check in to see what other companies do with last year comparisons with week 53?

Do you adjust last year comparisons in your apps to compare for example: 2025 week 53 with 2025 week 1? 

Or do you ignore week 53 completely?

This causes us a bit of pain every 5 years with shifting logic in our apps so I wanted to double check how other people do this?

Many thanks

[Chanty4u]

Add this in your master calendar 

WeekForComparison:

If(Week = 53, 52, Week) as WeekComp,

If(Week = 53, Year - 1, Year) as YearComp,

Then in YOY

Sum({<

   YearComp = {$(=Max(YearComp)-1)},

   WeekComp = {$(=Max(WeekComp))}

>} Sales)

[DanielleW1]

Thanks for your suggestion.

We were hoping to do exactly this, which does work in  most cases. However, we do trend analysis with weeks so because the week links to our sales fact table then it doesn't represent the correct last year week.

In theory, if 1 week is filtered and the week is a dimension, then it doubles up the rows so that it would look like:

Week   TY Sales   LY Sales

46          100             0

47            0               150

[marcus_sommer]

It's in general not possible to get an universal working comparison against groupings to classical calendars. Weeks are overlapping between months and years as well as month and years have different numbers of calendar-days and working-days.

In many scenarios is the resulting blurriness not essential to interpret the data and to derive appropriate actions respectively often have other factors much more impact. Nevertheless the effects could be minimized by comparing rates, maybe like:

sum(Sales) / count(distinct WorkingDays)

With more efforts specialized calendars in the direction of 445 or 454 or similar might be added and/or continuing month/week-fields are created, like:

year(Date) * 12 + month(Date) as Period

or maybe as offset like:

(year(today()) * 12 + month(today())) - (year(Date) * 12 + month(Date) as Period

whereby such measurements will increase the complexity and not each user will benefit from the advanced possibilities.