Hi everyone,
I try to create a measure to compare the actual weekly sales (for example for the fiscal week 50) to last year's weekly corresponding sales. I have a fiscal calendar that contains information about the current fiscal week and the previous year's week to which you should compare your actual sales.
My actual LY sales formula is working until I have a year with 53 fiscal weeks and now I have to find a new one that is able to use the fiscal calendar as a reference to know which last year week to use to do the comparison. For all the other weeks It's straightforward to do the comparison because the 52 first fiscal weeks are compared to the same week last year (202252 is compared to 202152), but week 202253 is compared to week 01 of 2022.
My actual formula is:
#SalesLY = CALCULATE([#Sales], 'Canada Financial Calendar'[YR_NUM]=2021)
I have a relationship between my fact table (sales) and the fiscal calendar table, my sales are by week and my calendar is by day then the link/key between them is the week identifier (example: fiscal week 50 of 2022 = 202250).
I use this comparison in a matrix table on which is applied different slicer/filter
I tried some formulas using SAMEPERIODLASTYEAR function but It's not working as wanted.
I tried different ways to accomplish this and I am now out of ideas.
All help will be appreciated.
Thank you.
