Having Trouble Creating a Rolling Aggregated Average

[machineReadable]

I feel like this should be simple, but my brain just isn't working today.

In the image below, I have a pivot table with the Headcount measure (which itself is a Countrows() within a Calculate()) aggregated by the Month-Year field in my date table. The Headcount | Average is a mockup of what I want the result to be, basically a rolling average of all of the months before it (i.e. Feb-19 is the average of 122 and 125, Mar-19 is the average of 122, 125 and 120, etc.), but I can't seem to figure it out.

This is my current formula:

=
AVERAGEX ( VALUES ( DimDate[Month-Year] ), [Headcount] )

At this point, wrapping [Headcount] in a Calculate() does nothing. The formula works in the grand total row, but in each month row I just get the same value as Headcount, so I'm pretty sure it's about removing the row context somehow, But my attempts so far have been unsuccessful. Note that the Headcount measure is a rollup of unique 'heads' that may exist in multiple aggregations, so I can't do a simple sum and divide by the number of months.

Thanks!

[RudyL]

Hi,

I suppose you have a table with a column for NAMES and for a MONTH. Let's call that table PEOPLE.
You probably have Headcount = CALCULATE(count(People[NAMES])) which will return the number of names per month.

Now try to make measure .... (this will count the running total for all headcount up till a certain month)

RunTotHeadcount = calculate(
count(People[NAMES]);
filter(
ALL(People);
People[month]<=max(People[month])
)
)
 
and measure.... (this will return a running total of months passed)
 
RunTotMonth = calculate(
DISTINCTCOUNT(People[month]);
filter(
ALL(People);
People[month]<=max(People[month])
)
)
 
And finally this will add up to the measure with the answer you need:
avgHeadcountYTD = [RunTotHeadcount]/[RunTotMonth]

Maybe there is an average function that does all this in one step, but I like to break it down 🙂

[Stachu]

Can you add sample tables (in format that can be copied to PowerBI) from your model with anonymised data? Like this (just copy and paste into the post window).

Column1	Column2
A	1
B	2.5

Also, what is the syntax for [Headcount]?

[machineReadable]

Headcount = COUNTROWS ( SUMMARIZE ( FactEmployment, FactEmployment[Employee Key] ) )

FactEmployment is a factless fact table that has a row for each employee, for each date they have been employed, and the role they are in, team they are on, etc. This way in my pivot table, I can group the headcount by those dimensions, and it reacts to any date range I select.

In this case I'm just doing a simple count, but using a table like this has been helpful in the past to create measures and use the entire table as a filter in the measure, although it is very resource intensive, so I'm looking for alternatives.