Forum Discussion

Quentin_B's avatar
Quentin_B
New Member
2 years ago

Period comparison problems using Weeks (ISO-8601)

Hi everyone, 

As a rookie on Power BI, I'm strugling for days on a date comparison using ISO 8601 weeks' standard.

Here is my 'Date' Table, as you can see, I created a column for the Year ISO ("Année ISO") and the last line is for the Week ISO ("Semaine ISO"). Looking at the results, both are working as the first line's date is "2018-12-31" but appears as Week n°1 and year 2019 is the last 2 columns.

 

The table 'fr_iso_Footfall' associated with is the one below. They are linked by 'st_date" column.
Again year_iso and week_iso are correct in this table (save the yellow numbers for later) :

 

 

Now, I'd like to compare on the same line, the results per week for 2023, 2022 and 2019 like the graph below:

The numbers for 2023 & 2022 are correct, but for 2019, it is not the good ones by 1 week.
If you look up the yellow numbers, I'm supposed to have for 'year_iso' = 2019 and 'week_iso' =1 -> 217 041
I have week_iso = 2 instead (300 322).


BUT if I add the lines for 2019, in this case, the good values are in the right places (see below)

 

So I can't understand why the data for 2019 alone are correct, but wrong when in comparison with 2023.
It looks like it is due to the link betwin the tables on "st_date" as I use the 'date'[date] to compare the dates in my measures (they are the end of the post) for 2022 and 2019.
As a confirmation, I tried to see if it changes the results if the link is set on "end_date", and yes Week_1 for year_2019 was correct, but the problem was then reported on weeks 52 or 53 if these were ending the year after (e.g: ISO_Year: 2022; ISO_Week: 52 ending date is 2023-01-01).

Any help would be highly appreciated 🙂

 

Here are my measures:
footfall_23  = SUM(fr_iso_Footfall[footfall])

 

footfall_22 = CALCULATE(fr_iso_Footfall[footfall_iso_N],SAMEPERIODLASTYEAR('Date'[Date]))

 

footfall_19 = CALCULATE(fr_iso_Footfall[footfall_iso_N],
   DATEADD ( 'Date'[Date],

   CALCULATE ( ( YEAR ( MAX ( 'Date'[Date] ) ) - 2019 ) * ( -1 ) ),
   YEAR ))

 

Sorry for the long post and thank you very much for those reading these lines. 
Have a good day.

 

1 Reply