Last 6 Weeks formula has stopped working due to new year.

[Shawry]

Hi everyone, 

My formula to calculate whether has stopped working now that we're in 2024. And I'm having trouble seeing what the problem is. It's the 'IsLast6Weeks?' column, and was working perfectly up until 31/12/2023. I've included a screenshot and have pasted the formula below. Thanks!

IsLast6Weeks? = IF('Date'[TodayWeekNum]-'Date'[Week Number]<7 && 'Date'[TodayWeekNum]-'Date'[Week Number]>0 && year(TODAY()) = 'Date'[Year],"Last 6 weeks", "False")

[jdbuchanan71]

Rather than using the week number you can just calculate the date of the start of the week 6 weeks ago and compare it to that.

Something like this.

IsLast6Weeks? = 
VAR _Today = TODAY()
VAR _6WeekStart = _Today - (35 + WEEKDAY( _Today ) ) +1
RETURN IF (
    [Date] >= _6WeekStart && [Date] <= _Today, 
    "Last 6 weeks", "False"
)