DAX - Average of a Distinct Count

[PeteDTG]

Hello,

Looking for some help with a DAX formula.  If you see the image below I am taking the distinct count of the employee_code field to obtain the number of employees per month.  However, I also need to show the last trailing 12 months as an average of the distinct counts.  How would I go about doing this in DAX? 

I can get a distinct count for the last 12 months using the formula below, but it needs to be an average and not a count.

Head Count LTM = CALCULATE(DISTINCTCOUNT(PaycomHours[Employee_Code]),DATESINPERIOD ('edw dimDate'[Date],MAX ( 'edw dimDate'[Date]),-12,MONTH))

Thank you!!

[amitchandak · Accepted solution]

@PeteDTG , Try a measure like

CALCULATE(averageX(values('edw dimDate'[month year]), calculate(DISTINCTCOUNT(PaycomHours[Employee_Code]))),DATESINPERIOD ('edw dimDate'[Date],MAX ( 'edw dimDate'[Date]),-12,MONTH))

[community_pinki · Accepted solution]

Hey @PeteDTG ,

Please try using this :-

Avg of Head Count LTM = CALCULATE(AVERAGEX(PaycomHours,DISTINCTCOUNT(PaycomHours[Employee_Code])),DATESINPERIOD
('edw dimDate'[Date],MAX ( 'edw dimDate'[Date]),-12,MONTH))

[PeteDTG]

You are awesome, thank you so much! Didn't think to use AVERAGEX! 🙂 

[amitchandak · Accepted solution]

@PeteDTG , Try a measure like

CALCULATE(averageX(values('edw dimDate'[month year]), calculate(DISTINCTCOUNT(PaycomHours[Employee_Code]))),DATESINPERIOD ('edw dimDate'[Date],MAX ( 'edw dimDate'[Date]),-12,MONTH))

[PeteDTG]

Thank you so much!! 😁