Moving Average based on Weeks. Slicers and sums

[Anonymous]

Goodday,

I have been trying for some time to make a moving average based on a Year-Week Hierarchy in a chart that listens to slicers. 

Sadly most of the posts on here that i find i can't seem to get working.

I have a chart that shows the amount ("Uur gedaan") for every week, based on the Year-Week hierarchy. This chart has slicers that change the data underneath based on other factors. 

I am trying to add a moving average of the past 6 weeks, that updates if i select something in the slicers.

The closest that i got was https://community.powerbi.com/t5/Desktop/Calculate-weekly-moving-average/m-p/530379.

But this just ends up always giving the current value divided by 6, instead of all past 6 values divided by 6. 

The table with the Amount has a Year Column, a Week Column and a Year-Week Concatenate to make a unique one for every year week Combo. My Date table has all 3 of these as well. The "Uur Gedaan" Column is summed up to the total for that week in the graph. 
Even then i am still unable to get it to work. Do you guys have any tips?

[Anonymous]

@amitchandak You are magnificant, thank you very much!

[Anonymous]

Hello Amitchandak, 

Thank you for your reply. I had my doubts about making a new column because those generally don't work if the data is spread over multiple rows. I will try to implement this solution next week and after that mark this as solved or reply if it hasnt been sovled yet 🙂

[amitchandak · Accepted solution]

@Anonymous , A measure like this usinf week rank column

Avg Last 6 weeks = CALCULATE(Averagex(Values('Date'[Week Rank]), [orders ]) , FILTER(ALL('Date'),'Date'[Week Rank]>=max('Date'[Week Rank])-6 && 'Date'[Week Rank]<=max('Date'[Week Rank])))

Use separate week/date table with a column like

Week Rank = RANKX(all('Date'),'Date'[Week Start date],,ASC,Dense)
OR
Week Rank = RANKX(all('Date'),'Date'[Year Week],,ASC,Dense) //YYYYWW format

Time Intelligence, DATESMTD, DATESQTD, DATESYTD, Week On Week, Week Till Date, Custom Period on Period,
Custom Period till date: https://youtu.be/aU2aKbnHuWs&t=145s