Previous Week Measure - Week 52 2019 and Week 1 2020

Question

I currently use the below measure to retrieve a value for the previous week, however as it's now week 1 of a new year, the field is returning "blank" as it cannot find any previous weeks.

How would I adapt my measure to include something along the lines of... if week 1 use the last week of the previous year, if not use the previous week.

VAR CURRENT_WEEK = WEEKNUM(TODAY()) return
CALCULATE(AVERAGE(DATA_TABLE[VALUE]), 
FILTER (DATA_TABLE, WEEKNUM(DATA_TABLE[DATE]) = CURRENT_WEEK -1))

Thanks in advance for your help

Answer 1

I would suggest using a start date and calculating the weeknum from that date. For example, if you choose 1 Jan 2018 as start date, then 1-7 Jan 2018 would be week 1 and first week in 2020 would be week 105. This would solve your issue.

The method you are using becomes really hard to handle if your data has multiple years. Weeknum 1 would denote the 1st week of 2020, 2019 and all the other years. This will mess up the calculation. The above method will make sure you have a unique number for a week.