Forum Discussion
Obtaining moving average from a measure
Hello everyone, in the table included below I have 2 measures that show me headcount and leaves. Now I need to turn these measures into a moving average for the headcount which is calculated from the months previous to the date selected in the date slicer.
- Additionally I need to get a measure that sums the leaves from the previous months to the date selected on the slicer from each matching company as shown in the table below.
New Expected table (Date Slicer January 2023):
| Company | Headcount | Leaves | Cummulative Leaves (From previous month) | Moving Average Headcount (From previous month to filtered date) |
| Food | 10 | 1 | null | null or 0 |
| Sports | 20 | 1 | null | null or 0 |
| Cars | 30 | 1 | null | null or 0 |
New Expected table Filtered (February 2023):
| Company | Headcount | Leaves | Cummulative Leaves (From previous month) | Moving Average Headcount (compared to previous month same company hc) |
| Food | 11 | 1 | 2 | 15.5 |
| Sports | 21 | 1 | 2 | 20.5 |
| Cars | 31 | 1 | 2 | 30.5 |
The Measures in the model are the following:
Current HC = CALCULATE(SUM('Forecast Query'[Forecast Value]),'Forecast Query'[Forecast Date]==MAX('Forecast Query'[Forecast Date]))
Leaves: L leaves are calculated with the
Moving Average Headcount Previous post solution =
VAR cur_date =
SELECTEDVALUE ( 'Table'[Date] )
VAR tmp =
FILTER ( ALL ( 'Table' ), 'Table'[Date] <= cur_date )
RETURN
AVERAGEX ( tmp, [Head count] )
1 Reply
- lbendlin
Super User
You cannot measure a measure. Implement the entire logic in its own measure.
Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216
Please show the expected outcome based on the sample data you provided.
https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523
