all i’m saying is your data correct when the data was combined. To me it looks like the employee number 6 was missing thats all.
Have you tried using datamentor/EDNA AI tools to see if it can help with a solution?
I put this in DataMentor and this what comes up.
I have a Matrix that shows a count of employees by reporting week.
I would like to be able to output a list of employee names when comparing 2 reporting weeks to identify what names appear in one week but not the other week.
If the count of employees is less than the previous week then they have left the business and I want to know who they are.
If the count of employees is greater than the previous week then there are new hires and I want to know who they are. If it is some combination of leaver and hires I would like to know the employee names.
Ideally, I would like to output this list via a drillthrough. I want to right click on a particular week’s headcount and be able to see the list of employees. I have tried using a combination of CONCATENATEX & EXCEPT, however, I have not been successful in outputting a list of names. is there a better solution?
Results:
To achieve this functionality in Power BI, you can follow these steps:
Dataset Preparation
- Ensure you have a dataset that includes employee names, reporting weeks, and respective statuses (e.g., still employed or left).
Measures for Identifying Changes
- Create measures to identify employees who left and those who were newly hired:
EmployeesLeft =
CONCATENATEX(
EXCEPT(
VALUES(Employee[EmployeeName]),
CALCULATETABLE(
VALUES(Employee[EmployeeName]),
PREVIOUSWEEK(CALENDAR[Date]),
ALL(Reporting[Week])
)
),
Employee[EmployeeName], ", "
)
NewHires =
CONCATENATEX(
EXCEPT(
CALCULATETABLE(
VALUES(Employee[EmployeeName]),
PREVIOUSWEEK(CALENDAR[Date]),
ALL(Reporting[Week])
),
VALUES(Employee[EmployeeName])
),
Employee[EmployeeName], ", "
)
Drillthrough Setup
- Create a new drillthrough page:
- Add a matrix visual and include the measure
EmployeesLeft for employees who left.
- Add another visual for
NewHires for newly hired employees.
- Set up the drillthrough using the relevant date filter (e.g., Reporting[Week]).
Applying Drillthrough
- Set the drillthrough feature on your matrix:
- Configure the matrix in your original report view where you have the employee counts by week.
- Right-click on a specific week’s headcount and use the drillthrough to navigate to your newly created detail page to see employees who left or were newly hired.
Combining Results
- If you need a combined list of both new hires and leavers, you can create an additional measure:
EmployeeChanges =
"New Hires: " & [NewHires] & " | Leavers: " & [EmployeesLeft]
- Add this measure to your drillthrough page.
Final Result
With these steps, you can right-click on a week’s headcount and drillthrough to see detailed employee lists for that specific week. Customize this setup according to your data structure and specific needs.
Note
Ensure that your data relationships and calendars are correctly set up in Power BI. This will enable accurate calculations when using time-based functions like PREVIOUSWEEK.
If you encounter any specific issues or errors, please provide details so that I can help troubleshoot further.
thanks
Keith
