Help Needed :( I am trying to create 4 week trailing average that is dynamic :(

[BabyBinki821]

Hi

I am going crazy trying to figure this out. I want to create a dynamic 4 week trailing average of the number of orders that our company received. My goal is to have a column bar chart with orders and then a line with the 4 week trailing orders average.

There are two other conditions: 1) I have a column of week ending dates all on Fridays that I need to use 2) I have another column with two options: 1) Ordered 2) Canceled - I only need Ordered.

I have watched over 10 videos and going through the message board and can't figure it out. Screenshots below: 

[amitchandak · Accepted solution]

@BabyBinki821 , Create a rank on week ending date

new column

week rank = rankx(Date, Date[Week End],,asc,dense)

First measure

orders = countrows(filter(Table, Table[Order Status] ="Ordered"))

4 week rolling

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

Avg  4 week rolling

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

prefer a date table

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-La...
https://www.youtube.com/watch?v=pnAesWxYgJ8