DAX YoY last n weeks - SSAS

[Anonymous]

Turnover by weeknumberPower Bi Novice here.

I am ussing SSAS tabular cube as my data source, where I want to show YoY for 3 years for last 6 weeks only, eg. week 21 - week 27 in this graph.

I can't do week offset in Power query. Has to be DAX, and I am not able to figure out best way to do it. Would appreciate any ideas I can get.

[amitchandak · Accepted solution]

@Anonymous , You need to try measure like

This year last 6 week =
var _max = maxx(allselected('Date'), 'Date'[Week])
var _min = minx(allselected('Date'), 'Date'[Week]) -6
return
CALCULATE(sum('Table'[Qty]), FILTER(('Date'),'Date'[Year]=max('Date'[Year]) && 'Date'[Week] <= _max &&'Date'[Week] >= _min ))

last year last 6 week =
var _max = maxx(allselected('Date'), 'Date'[Week])
var _min = minx(allselected('Date'), 'Date'[Week]) -6
return
CALCULATE(sum('Table'[Qty]), FILTER(('Date'),'Date'[Year]=max('Date'[Year])-1 && 'Date'[Week] <= _max &&'Date'[Week] >= _min ))