Forum Discussion

abrown3893's avatar
abrown3893
New Member
2 years ago

Issue with calculating Headcount rolling average

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 ...
Need Help
Ashish_Mathur's avatar
Ashish_Mathur
Icon for Super User rankSuper User
2 years ago

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.