Forum Discussion
Current Week versus Same Week Last Year
Hello all,
I am trying to create a custom measure in order to get same week from last year. I work with US calendar and every week restarts on Sunday. Also some years have 52 weeks, others have 53.
I am trying to compare current week with same week last year. Eg: Compare Week 3 year 2023 with Week 3 year 2022. Since there is no time intelligence function that can be used in this case (as most of them compares the same period (same days), but not the same weeks (which can have different days), I need to create a custom measure in order to have an accurate result. Also, I do have other breakdowns in the page, beside the year, quarter, month, week.
Does anybody has any ideas for same week last year measure?
Thank you!
MeasureSameWeekLastYear = //Try this one might help you VAR CurrentYearWeek = CONCATENATE(MAX('YourTable'[Year]), "-", MAX('YourTable'[WeekNumber])) VAR PreviousYearWeek = CONCATENATE(MAX('YourTable'[Year]) - 1, "-", MAX('YourTable'[WeekNumber])) RETURN CALCULATE( [YourMeasure], FILTER( ALL('YourTable'), 'YourTable'[YearWeek] = PreviousYearWeek ) )
3 Replies
MeasureSameWeekLastYear = //Try this one might help you VAR CurrentYearWeek = CONCATENATE(MAX('YourTable'[Year]), "-", MAX('YourTable'[WeekNumber])) VAR PreviousYearWeek = CONCATENATE(MAX('YourTable'[Year]) - 1, "-", MAX('YourTable'[WeekNumber])) RETURN CALCULATE( [YourMeasure], FILTER( ALL('YourTable'), 'YourTable'[YearWeek] = PreviousYearWeek ) )Hi,
Thank you for the quick answer! However, this measures seems not to work. I cannot declare the 2 VARs, as it says the sintax is incorrect.
Hi! Quick update on this topic. The measure is working - I just have updated a little bit. I have directly used a column from my calendar table in the variables - I did not use "Concatenate".
Thanks for your help!
