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 we...
Need Help
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