Forum Discussion

Shawry's avatar
Shawry
Icon for Helper I rankHelper I
1 year ago

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

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 😕

 

Need Help

2 Replies

  • bhanu_gautam's avatar
    bhanu_gautam
    Icon for Super User rankSuper User
    1 year ago

    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's avatar
      Shawry
      Icon for Helper I rankHelper I
      1 year ago

      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