data between different years

[federico_fenu]

Hi all,

i have this table in excel about the turnover of a store, in excel the problem is that when i am in the weeks across 2 years (see the pic) i don't see the data of the year before, for example week -2,-1 and 0 should be 50, 51, and 52 ( according to our commercial calendar.

This way the turnover for 2024 compares the same weeks of 2023 and the turnover of 2023 with the same weeks of 2022.

Any way of set this in power bi?

[SamWiseOwl]

Hi @federico_fenu 

Without seeing the structure of the data it would be hard to be sure.

My guess is the cell 2024(c), Week -2 has two filters that clash on it. 

I assume Calendar[Week] is a column in your calendar table.

This joins via Calendar[Date] to your DataTable[Date].

When you filter the Calendar[Week] = -2 and Calendar[Year] = 2024 you are removing the 2023 data.

Does that sound correct?

If so you will need tweak your measure to change the Calendar[Year] filter.

[federico_fenu]

the image is still from excel.

I want to replicate it in power bi correcting tha wrong situation, the problem with our data structure comes from our commercial calendar of 52 weeks, it can happen that week 52 can be split between 2 years, so the first days of the new year are week 0 but still have to be sum to the last days of previous year to have the complete week 52 

[SamWiseOwl]

Ahh Avi Singh did some superb videos on how to do offset week calendars:
https://www.youtube.com/watch?v=NKr2TizM-KU

And how to do relative week numbers:
https://www.youtube.com/watch?v=INaRk1Cbqmg

Finally he did a video all about his free calendar table:

https://www.youtube.com/watch?v=CJ2y6v4mQZk

Give them a skim and see if it does what you need!