5 Day Moving Average By Day - Help Please

DAX DAX Calculations
1

Hello Everyone,

I need some help please, I am
Trying to create a moving 5 day average by day for my Power BI report

Currently I have a calendar table as my primary date and that is connected to our revenue table which shows each transaction,

I also have a measure called revenue which is the below DAX Measure

Revenue = SUM(RevenueTable’[JobunadjustedValue]

The measure I have been trying to create is the below but for some reason it works for the first day then the following days it doesn’t calculate properly

5dayMovingValue =

VAR NumOfdays = 5
VAR LastSelectedDate = MAX ( ‘CalendarTable’[Date]
VAR Period =
DATESINPERIOD ( CalendarTable’[Date], LastSelectedDate, -NumOfdays, DAY )
VAR Result =
CALCULATE(
AVERAGEX(
VALUES (CalendarTable’[Date]),
Revenue
),Period

Return Result

My ideal output result is below

Date. 5DayMovingAverage
13/05/2022 £95,000
12/05/2022. £94,765
11/05/2022. £94,500
10/05/2022. £93,100

Thanks

2 Likes
2

Can you post a PBIX file also please? Will make it easier for people to help.

3

@daniel.costin

Try this. I did something similar for 3-month rolling average. I think just change it from month to day.

Rolling Average 3 months =
VAR LastDate_ =
LASTDATE ( ‘Date’[Date] )
RETURN
CALCULATE (
AVERAGEX ( VALUES ( ‘Date’[Date] ), CALCULATE ( SUM ( ‘Line Item Win Rate’[GM % Qtd.] ) ) ),
FILTER (
ALL ( ‘date’ ),
[Date].[Day] <= LastDate_
&& [Date].[Day] > DATEADD ( LastDate_, -3, MONTH )))

4

Hello @daniel.costin, did the response provided by @Paul.Gerber help solve your query? It’s been a while since we got a response from you. In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved.

5

Hi @daniel.costin, due to inactivity, a response on this post has been tagged as “Solution”. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the check box.