Distinct Count Most Recent Data by Employee for Selected Period
The issue: When trying to apply a distinct count to employee history I am receiving accurate totals but the counts by a particular dimension are off (Location, Title, or Department). I have created a simplified data model to illustrate the issue.
What do I mean?
There are only 2 employees at this fake company, Bob and Mike. The company is frequently giving promotions, moving employees around departments, and geographical locations.
We need to be able to dynamically establish headcounts for the various dimensions (Location, Title, or Department) for any given time range selected. For example in the PBIX we have selected Q1 and Q2 for 2021. Part way into 2021, Bob was promoted from an Intern to an Associate and Mike moved from Dalls to Reno. I have given a preview of the transactional data in the upper left table.
Now if we reference the smaller matrixes and tables we can see the employee counts by the various dimensions. In many of the dimensions it appears we 3 employees!
In an attempt to solve the issue myself I created a calculated column in the Employee History table which ranks the transactional data by employee. This appears to be working as the transactions are ranked properly.
VAR TkinitRank = ‘Employee History’[Employee ID]
FILTER( ‘Employee History’ , ‘Employee History’[Employee ID] = TkinitRank ) ,
‘Employee History’[PeriodEndDate] ,
‘Employee History’[PeriodEndDate] , DESC , Dense )
My next thought was using this rank to limit the Headcount measure (using DISCTINCTCOUNT) to only look at the most recent transactional data for each employee to determine the count.
This did not work, as can be seen from the snip above.
One measure which can be used to determine the headcount for any dimension based on the date range selected in the slicer. The measure would use the most recent data for each employee for that period. By the slicer selection in the snip, no one would show in Dallas (Mike moved to Reno) and there would be no intern (Bob was promoted).
Basically I would like the distinct count to only pay attention to the row corresponding to the last PeriodEndDate (dynamically filtered by the date slicer) by employee.
Any assistance would be greatly appreciated.
PBIX is here:
Distinct Count Most Recent Data by Employee for Selected Period.pbix (82.9 KB)