Calculate average of every 4 last values

DAX DAX Calculations
1

Hello
I need to calculate the average of every 4 last values. I have the cumulative average working well but I’m stuck on how to calculate the running average of every 4 values. Please find attached my PBix file with sample data.

Thanks heapsCumul_Average_Problem.pbix (123.1 KB)

2

Hi Fiona,

Think you’ll be able to achieve this with TOPN because that creates a table you can iterate over but then you’ll have to change the granularity of your Dates table to include time as well…

3

Hope below works:

CALCULATE (
Total Values,
DATESINPERIOD(Date[Date],
LASTDATE(Date[Date]),-4,DAY))/4

4

Hello
Thank you @Mohammed_ali and @Melissa for your replies.

Actually I finally found a solution. Basically I used RankX to create a RankIndex column based on the datetime column in ASC order. I then calculated the values based on the Max of the RankIndex column.

Attached is my PBix solution file with the desired outcome for anyone who is interested. This is a running total/average of every 4 values. Note in my file how I went back 3 values each time ie Max(RankIndex)-3

Thanks
export_values.csv (10.9 KB) Cumul_Average_Problem_Solution.pbix (138.3 KB)

2 Likes