Get Moving Average from an existing measure

[tom1tas]

Hey Everyone, thanks for checking my message,
I am trying to get a cummulative average  but I dont know how, can you help me?

Currently my power BI data is designed the following way, my headcount data  is calculated using this measure 

Current HC = CALCULATE(SUM('Forecast Query'[Forecast Value]),'Forecast Query'[Forecast Date]==MAX('Forecast Query'[Forecast Date])) so power bi wont allow me to do an average of my measure, please help me identify how to get the cummulative average column shown below.

Date	Headcount	Leaves	Cummulative Average Headcount	Cummulative leaves
1/1/2022	200	10	200	10
2/1/2022	201	7	200.5	17
3/1/2022	198	3	197.666667	20

[Anonymous · Accepted solution]

Hi @tom1tas ,

Please try below steps:

1. below is my test table

Table:

2. create a measure with below dax formula

Cummulative Average Headcount =
VAR cur_date =
    SELECTEDVALUE ( 'Table'[Date] )
VAR tmp =
    FILTER ( ALL ( 'Table' ), 'Table'[Date] <= cur_date )
RETURN
    AVERAGEX ( tmp, [Head count] )

3. add a table visual with fields and measure

Please refer the attached .pbix file.

Best regards,
Community Support Team_Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[tom1tas]

Hey thank you it worked with tha date table, how about getting the moving average for the same measure (CALCULATE(SUM('Forecast Query'[Forecast Value]),'Forecast Query'[Forecast Date]==MAX('Forecast Query'[Forecast Date])) )- but now filtered by another attribute, company.  The default date will be a year/month slicer. 

I need help with two errors, the first one is that my cummulative leaves measure will not work when I change the table columns to agency, I  need it to do a running total for all the leaves previous to the slicer selected date. Same for the moving average of the headcount, it would need to be calculated from all the dates previous to the slicer selected date .

Company	Headcout Measure	Leaves	Cummulative Leaves (Failing)	Average HC(Failing)
sports	1
streaming	55
gaming	1
mma	1	5	5
mba	19
cereals	1
kids books	100	5	5

[tom1tas]

Notice time intelligence functions will not work because the measure of the headcount is not calculated on continuous date data.