Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!!
Solved! Go to 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))
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))
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))
You are awesome, thank you so much! Didn't think to use AVERAGEX! 🙂
@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))
Thank you so much!! 😁
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
47 | |
23 | |
20 | |
15 | |
13 |
User | Count |
---|---|
51 | |
42 | |
39 | |
19 | |
19 |