@VincentF one more thought that came to my mind you need to watch out for.
Depending on how your data is structured you need to choose the proper join condition. What I mean by that is, imagine this case of product chocoloate. This has sales in every month of 2024, but only in may and june of 2025.
If you would bring in the last_year value only by left-joining pk with LY_pk you would miss all the yellow marked last_year sales due to this month not existing in the current year. So you would calculate a false last_year_sales sum, since you are not incorporating those months where you had sales in last year, but don’t have in current year.
In order to prevent this, you either first explode your left table for all products over all possible time dimensions (and set sales to 0 on these) so that the left-join will always work.
Or you just go for the Outher join and do a bit of cleaning afterwards.
See here with the outer join, all month in 2024 that have either sales in 2024 or in the last year reference 2023 (yellow) are appearing. 2024_06 is not getting last year sales since 2023_06 is not existing (this is ok).
The only thing left here to do is to clean up your dimension columns for those cases, where you don’t have current year, but last year sales (For example:
pk = coalesce(ok, right_pk)
