Calculate Rolling Average for Prior Year

[Anonymous]

Hello all,

I have a measure that calcualtes Prior year sales. When I am trying to calculate Rolling 4 week average of Prior Year sales its not working. I dont know what mistake I am doing.Please help.

Here are my calculations:

Prior Year Sales= 

CALCULATE (

[Current Year SA],

FILTER (

ALL ( CalendarAgg ),

CalendarAgg[FiscalYearNum]

= SELECTEDVALUE ( CalendarAgg[FiscalYearNum] ) - 1

&& CalendarAgg[FiscalWeekNumOfFiscalYear] = SELECTEDVALUE ( CalendarAgg[FiscalWeekNumOfFiscalYear])

)

)

PriorYearRolling 4 wk SA =

CALCULATE (

Prior Year Sales,

FILTER(

ALLSELECTED( CalendarAgg),

CalendarAgg[FiscalWeekNumOverall] <= MAX ( CalendarAgg[FiscalWeekNumOverall])

&& CalendarAgg[FiscalWeekNumOverall]

> MAX ( CalendarAgg[FiscalWeekNumOverall] ) - 4

)

)

My data Looks like this 

[amitchandak]

@Anonymous , Try these measure with week or week rank. For week rank refer my blog

1 year back rolling 4 =
var _week = maxx(allselected(Date), Date[WEEK])
var _year = maxx(allselected(Date), Date[Year])

return
CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Year]=_year-1 && 'Date'[Week] <= _week && && 'Date'[Week] >= _week -4))

or

1 year back rolling 4 =
var _week = maxx(allselected(Date), Date[WEEK RANK])
var _year = maxx(allselected(Date), Date[Year])

return
CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Year]=_year-1 && 'Date'[Week RANK] <= _week && && 'Date'[Week RANK] >= _week -4))

Power BI — Week on Week and WTD
https://medium.com/@amitchandak.1978/power-bi-wtd-questions-time-intelligence-4-5-98c30fab69d3
https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-Last-Week/ba-p/1051123
https://www.youtube.com/watch?v=pnAesWxYgJ8