Rolling 8 week day Average (for a particular day of week)

[mws5872]

I am trying to create a measure that shows the average of the last 8 days for the date displayed. The below graph in orange shows the daily amount, the blue shows the average for the last 8 days for the date. The result will look something like this:

I have also included a excel file that shows the actual calculation I am trying to obtain in two examples. (3/17 - Tuesday and 3/5 - Thursday)  There is also some sample data included as well.

here is the link to the example excel file ! 

https://drive.google.com/open?id=1Bf_Q0IxgAWw2ysxGkEUrRnB5aiyEkLqQ

any help appreciated!

This visual shows the data points used to calculate the 3/17 and 3/5 examples. I am pretty sure need to do a calculate and averagex but not sure how to approach on this!

[v-lili6-msft · Accepted solution]

hi  @mws5872 

For your case, you could just try this simple way as below:

Step1:

Add a weekday column for date

Step2:

Create a measure as below:

Measure = 
DIVIDE(CALCULATE(SUM('Table'[amount]),FILTER(ALL('Table'),'Table'[WeekDay]=MAX('Table'[WeekDay])&&DATEDIFF('Table'[Date],MAX('Table'[Date]),DAY)>0&&DATEDIFF('Table'[Date],MAX('Table'[Date]),DAY)<=57)),
COUNTROWS(FILTER(ALL('Table'),'Table'[WeekDay]=MAX('Table'[WeekDay])&&DATEDIFF('Table'[Date],MAX('Table'[Date]),DAY)>0&&DATEDIFF('Table'[Date],MAX('Table'[Date]),DAY)<=57)))

or

Measure 2= 
DIVIDE(CALCULATE(SUM('Table'[amount]),FILTER(ALL('Table'),'Table'[WeekDay]=MAX('Table'[WeekDay])&&DATEDIFF('Table'[Date],MAX('Table'[Date]),DAY)>0&&DATEDIFF('Table'[Date],MAX('Table'[Date]),DAY)<=57)),
8)

Result:

Regards,

Lin

[mws5872]

@v-lili6-msft  thank you for this. I implemented this and so far it seems to be working out. This was immensly helpful.  
I am not an expert in DAX is there a way you can breakdown and explain how you approached this ? would be helpful for me and other users that come back later! 

[Tad17]

Hey @mws5872 

You ca ndo this using DAX, but really Power BI can do so much more.

I recommend creating a matrix that has Date as rows, select the drop down menu in the fields pane for date, make sure date hierarchy is selected and then delete everything except week.

Then you can can create a card visual with a measure for the average using AVG = AVERAGE(Table1[Amount])

Then simply put in a timeline splicer (such as this one: https://appsource.microsoft.com/en-us/product/power-bi-visuals/WA104380786?tab=Overview) and you ca nset the slicer to the desired 8 week time period or use any other time period!

If you are trying to do it using DAX and all you need is a simple Card visual with the Average you can use a regular slicer with the date and then use the SELECTEDVALUE and DATEADD function to find the period you need. 

SELECTEDVALUE: https://docs.microsoft.com/en-us/dax/selectedvalue-function

DATEADD: https://docs.microsoft.com/en-us/dax/dateadd-function-dax

If this helps please kudo.

If this solves your problem please accept it as a solution.

[amitchandak]

@mws5872 ,

I have this file, which uses rank to deal with Week. There are a rolling 12 weeks. Change it to 8.

https://www.dropbox.com/s/d9898a48e76wmvl/sales_analytics_weekWise.pbix?dl=0

[mws5872]

unfortunately I am not dealing with weeks. please look at the excel I am dealing with individual days.

[Greg_Deckler]

So, this?

https://community.powerbi.com/t5/Quick-Measures-Gallery/Rolling-Average/m-p/160720#M3

[mws5872]

no that is not correct. 

I think if we could do something like this making a value for each date and filter on say all 3s and take the last 8 of those and average.

1 Sunday

2 Monday

3 Tuesday

4 Wednesday

5 Thursday

6 Friday

7 Saturday

[Tad17]

@mws5872 

Here you go: https://docs.microsoft.com/en-us/dax/day-function-dax

DAY() Function

[mws5872]

no that is not correct... I will try to re-write my question. thanks!!

[Tad17]

@mws5872 That's fine. Power BI will create a data hierarchy for you. And if there are gaps in the days you can just create a calendar table and link it to your date column via relationships and use it on your timeline slicer.

[mws5872]

@Tad17 the problem with this approach is I will then do a bar chart of the last 8 to 9 days the orange is the actual amount for that day while the blue is the 8 data point average for the previous weeks ( on the respective day) so if you look at 3-2 the blue shows the last 8 mondays averaged ( just monday alone) while 3-3 shows the last 8 tuesdays only averaged. 

does that make sense ? 

[Greg_Deckler]

Rolling Weeks? https://community.powerbi.com/t5/Quick-Measures-Gallery/Rolling-Weeks/m-p/391694#M128