Forum Discussion

Shawry's avatar
Shawry
Helper I
2 years ago
Solved

Prior period comparison

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!

Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    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

2 Replies

  • lbendlin's avatar
    lbendlin
    Super User
    2 years ago

    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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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