Prior period comparison

[Shawry]

Hi, 

I'm wanting to display "Last 6 weeks" based on off the week ending, and also display "Last 6 weeks prior period" so that we can compare how the exact same 6 weeks were the year before. I have the following formula, which works for the  "Last 6 weeks" but not the "Last 6 weeks prior period". 

I'm assuming it's to do with the logic in:

    'Date'[Year] = YEAR(TODAY()) - 1 &&

        'Date'[Week Number] - 'Date'[TodayWeekNum] > 52 - 6

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

 Can anyone please help out? Thanks!

[Anonymous · Accepted solution]

Thanks for the reply from @lbendlin , please allow me to provide another insight: 

Hi  @Shawry ,

Here are the steps you can follow：

1. Create calculated column.

Past 6 Weeks or Prior Period =
var _today=TODAY()
var _weektoday=WEEKNUM(_today,2)
var _currentmaxdate=MAXX(FILTER(ALL('Table'),YEAR('Table'[Date])=YEAR(_today)&&'Table'[Week]=_weektoday-1),[Date])
var _currentmindate=MINX(FILTER(ALL('Table'),YEAR('Table'[Date])=YEAR(_today)&&'Table'[Week]=_weektoday-6),[Date])
var _lastmaxdate=MAXX(FILTER(ALL('Table'),YEAR('Table'[Date])=YEAR(_today)-1&&'Table'[Week]=_weektoday-1),[Date])
var _lastmindate=MINX(FILTER(ALL('Table'),YEAR('Table'[Date])=YEAR(_today)-1&&'Table'[Week]=_weektoday-6),[Date])
return
SWITCH(
    TRUE(),
    'Table'[Date]>=_currentmindate&&'Table'[Date]<=_currentmaxdate,"Last 6 weeks",
    'Table'[Date]>=_lastmindate&&'Table'[Date]<=_lastmaxdate,"Last 6 weeks prior period","False")

2. Result:

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[lbendlin]

years and weeks are incompatible.  If you want to compare like for like you need to use TODAY()-35 to TODAY() for the current period and TODAY-399 to TODAY()-364 for the prior period.