DAX - How to sum a measure over the last N weeks, this year and last year

0
votes

I have a measure [Total] reported by a custom week-ending date field 'Date'[CustWEndingDate] from Date table, based on Sat - Fri weeks (so each week ends on a Fri), plus an associated 'Date'[WeekNum] and 'Date'[Year] to that. Data looks like this:

[CustWEndingDate], [Year], [WeekNum], [Total]
3/29/2019, 2019, 13, 400
4/5/2019, 2019, 14, 350
4/12/2019, 2019, 15, 420
4/19/2019, 2019, 16, 390
...
3/27/2020, 2020, 13, 315
4/3/2020, 2020, 14, 325
4/10/2020, 2020, 15, 405
4/17/2020, 2020, 16, 375

My question is this: How do I create DAX measure to calculate last 3 weeks this year OVER same last 3 weeks last year? For example, week 14, 15 and 16 this year (325+405+375) vs same week 14, 15 and 16 last year (350+420+390)?

Thank you in advance for any help you can provide!

1
dax

1 Answers

0
votes

I would use the following approach:

  1. Calculate scalar with today's date
  2. Calculate scalar with year of today's date
  3. Calculate scalar with WeekNum associated to today's date
  4. Calculate scalar with CY value for last 3 weeks this year
  5. Calculate scalar with PY value for (same) last 3 weeks previous year
  6. Calculate ratio

Here is the technical implementation:

Joel's Measure:=
    VAR _TODAY = TODAY()
    VAR _YEAR = YEAR(_TODAY)
    VAR _WEEKNUM = CALCULATE(MIN('Date'[WeekNum]), 'Date'[Date] = _TODAY)
    VAR _CY = CALCULATE([MEASURE], 'Date'[Year] = _YEAR, 'Date'[WeekNum] IN {_WEEKNUM, _WEEKNUM - 1, _WEEKNUM - 2}) 
    VAR _PY = CALCULATE([MEASURE], 'Date'[Year] = _YEAR - 1, 'Date'[WeekNum] IN {_WEEKNUM, _WEEKNUM - 1, _WEEKNUM - 2}) 
    RETURN
    DIVIDE(_CY - _PY, ABS(_PY))

This should solve your problem. If not, please share a feedback with the result.