Last 6 weeks AND Prior Year Comparison Measure stopped working due to moving into a new year

[Shawry]

Hi, 

I have a formula in my workbook which has stopped working due to moving into a new year i.e. being in 2025 now and the MonthNumber resetting to 1. 

The formula was to detect whether a week was within the past 6 weeks or last 6 weeks from the previous year but it has broken. 

Can anyone see any way of fixing this when looking at the following formula? Or taking a different approach? 

Past 6 Weeks or Prior Period = 
IF(
    (
        'General Enquiries - Weekly'[TodayWeekNum] - 'General Enquiries - Weekly'[Week Number] < 7 &&
        'General Enquiries - Weekly'[Week Number] >= 0 &&
        'General Enquiries - Weekly'[Year] = YEAR(TODAY())
    ),
    "Last 6 weeks",
    IF(
        'General Enquiries - Weekly'[TodayWeekNum] - 'General Enquiries - Weekly'[Week Number] < 7 &&
        'General Enquiries - Weekly'[TodayWeekNum] - 'General Enquiries - Weekly'[Week Number] >= 0 &&
        'General Enquiries - Weekly'[Year] = YEAR(TODAY())-1,
        "Prior Period Comparison",
        "False"
    )
)

This is obviously not working as there are weeks within the last 6 weeks that are spread over 2024 and 2025, and have week numbers of 50 -52 etc and 1. I need to be able to handle checking for these but am utterly stuck on how to achieve this. 

I also tried using ChatGPT to generate the below, but to no avail. 

Past 6 Weeks or Prior Period = 
VAR CurrentYear = YEAR(TODAY())
VAR CurrentWeekNum = 'General Enquiries - Weekly'[TodayWeekNum]
VAR ComparisonWeekNum = 'General Enquiries - Weekly'[Week Number]
VAR ComparisonYear = 'General Enquiries - Weekly'[Year]
VAR WeeksDifference = CurrentWeekNum - ComparisonWeekNum + (CurrentYear - ComparisonYear) * 52

RETURN
    IF(
        WeeksDifference < 7 && WeeksDifference >= 0,
        IF(
            ComparisonYear = CurrentYear,
            "Last 6 weeks",
            IF(
                ComparisonYear = CurrentYear - 1,
                "Prior Period Comparison",
                "False"
            )
        ),
        "False"
    )

Please help me 😕

[bhanu_gautam]

@Shawry , Try using

Past 6 Weeks or Prior Period =
VAR CurrentYear = YEAR(TODAY())
VAR CurrentWeekNum = 'General Enquiries - Weekly'[TodayWeekNum]
VAR ComparisonWeekNum = 'General Enquiries - Weekly'[Week Number]
VAR ComparisonYear = 'General Enquiries - Weekly'[Year]
VAR WeeksDifference =
IF(
ComparisonYear = CurrentYear,
CurrentWeekNum - ComparisonWeekNum,
IF(
ComparisonYear = CurrentYear - 1,
CurrentWeekNum + (52 - ComparisonWeekNum),
999 // A large number to ensure it doesn't fall into the last 6 weeks category
)
)

RETURN
IF(
WeeksDifference < 7 && WeeksDifference >= 0,
IF(
ComparisonYear = CurrentYear,
"Last 6 weeks",
IF(
ComparisonYear = CurrentYear - 1,
"Prior Period Comparison",
"False"
)
),
"False"
)

[Shawry]

Hi @bhanu_gautam 

Thanks for your reply. 

I've tried your solution, but it is flagging the last 6 weeks as 'Prior Period Comparison', as you'll see below. Similar issue to what I'm having and can't seem to figure it out. Any other ideas or solutions?

Thanks