Issue with calculating Headcount rolling average

[abrown3893]

Hello, I am trying to calculate rolling average headcount from row data based on a count of employee IDs (ID). My data includes employee ID, Name, and Period. The "Period field" is the date that the headcount was pulled and is from Dec 2023 back to January of 2022. 

I am trying to create a measure to dynamically give me the average headcount across the last 12 months. Currently, I seem to just be getting the sum of employee IDs for each month. I have been reading some articles and it seems that SUMX might be helpful but I can't seem to grasp how to insert that into my code effectively and not sure if it should replace COUNT or be used in conjunction with COUNT. 

1YearAverage =

CALCULATE(

    COUNT('Headcount Data'[ID]),

    DATESINPERIOD('Headcount Data'[Period], LASTDATE('Headcount Data'[Period]),12,MONTH))

    /

    CALCULATE(

    DISTINCTCOUNT('Headcount Data'[Period]),

    DATESINPERIOD('Headcount Data'[Period], LASTDATE('Headcount Data'[Period]),12,MONTH))

Any ideas how to make this count the number of IDs for each month and then take the latest month + average the last 12 months dynamically?

[v-zhangti]

Hi, @abrown3893 

Can you provide sample data for testing? Sensitive information can be removed in advance. What kind of expected results do you expect? You can also show it with pictures or Excel. I look forward to your response.

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Ashish_Mathur]

Hi,

Try this approach.

1. Create a Calendar Table with calculated column formilas for Year, Month name and Month number.  Sort the Month name column by the Month number
2. Create a relationship (Many to One and Single) from the Date column of Table to the Date column of the Calendar Table
3. to your visual/slicer/filter, drag Date//Month name from the Calendar Table
4. Write this measure

Number of employees = distinctcount('Headcount data'[id])

Average headcount of 13 months ended the current month = averagex(summarize(calculatetable(calendar,datesbetween(calendar[date],edate(min(calendar[date]),-12),max(calendar[date]))),calendar[year],calendar[month name],"ABCD",[Number of employees]),[ABCD])

If this does not help, then share the download link of the PBI file and show the expected result very clearly.