Forum Discussion
DAX formula
Hello Community Member
This is my sample data
| EMpID | Process Name | Joining date | R1 Start date | R2 Start date | R3 Start date | BAU Start Date | Capacity End date |
| ABC123 | Process 1 | 7-Mar-23 | 14-Mar-23 | 21-Mar-23 | 28-Mar-23 | 4-Apr-23 | 31-Dec-23 |
| ABC124 | Process 1 | 7-Mar-23 | 14-Mar-23 | 21-Mar-23 | 28-Mar-23 | 4-Apr-23 | 31-Dec-23 |
| ABC125 | Process 1 | 7-Mar-23 | 14-Mar-23 | 21-Mar-23 | 28-Mar-23 | 4-Apr-23 | 31-Dec-23 |
| ABC126 | Process 1 | 7-Mar-23 | 14-Mar-23 | 21-Mar-23 | 28-Mar-23 | 4-Apr-23 | 31-Dec-23 |
| ABC127 | Process 2 | 16-Mar-23 | 23-Mar-23 | 30-Mar-23 | 6-Apr-23 | 13-Apr-23 | 31-Dec-23 |
| ABC128 | Process 2 | 16-Mar-23 | 23-Mar-23 | 30-Mar-23 | 6-Apr-23 | 13-Apr-23 | 31-Dec-23 |
| ABC129 | Process 2 | 16-Mar-23 | 23-Mar-23 | 30-Mar-23 | 6-Apr-23 | 13-Apr-23 | 31-Dec-23 |
| ABC130 | Process 2 | 10-Apr-23 | 17-Apr-23 | 24-Apr-23 | 1-May-23 | 8-May-23 | 31-Dec-23 |
| ABC131 | Process 2 | 10-Apr-23 | 17-Apr-23 | 24-Apr-23 | 1-May-23 | 8-May-23 | 31-Dec-23 |
| ABC132 | Process 2 | 10-Apr-23 | 17-Apr-23 | 24-Apr-23 | 1-May-23 | 8-May-23 | 31-Dec-23 |
Expected Output for process 1(SLicer)
| Filter as Process 1 | |||||
| Week Start Date | Employee Joined | Emp in R1 Stage | Emp in R2 Stage | Emp in R3 Stage | Emp in BAU |
| 6-Mar-23 | 4 | ||||
| 13-Mar-23 | 4 | ||||
| 20-Mar-23 | 4 | ||||
| 27-Mar-23 | 4 | ||||
| 3-Apr-23 | 4 | ||||
| 10-Apr-23 | 4 | ||||
| 17-Apr-23 | 4 | ||||
| 24-Apr-23 | 4 | ||||
| 1-May-23 | 4 | ||||
| 8-May-23 | 4 | ||||
| 15-May-23 | 4 | ||||
| 22-May-23 | 4 | ||||
| 29-May-23 | 4 |
Expected Outcome Process 2
| Filter as Process 2 | (Filter/Slicer) | ||||
| Week Start Date | Employee Joined | Emp in R1 Stage | Emp in R2 Stage | Emp in R3 Stage | Emp in BAU |
| 6-Mar-23 | |||||
| 13-Mar-23 | 3 | ||||
| 20-Mar-23 | 3 | ||||
| 27-Mar-23 | 3 | ||||
| 3-Apr-23 | 3 | ||||
| 10-Apr-23 | 3 | 3 | |||
| 17-Apr-23 | 3 | 3 | |||
| 24-Apr-23 | 3 | 3 | |||
| 1-May-23 | 3 | 3 | |||
| 8-May-23 | 6 | ||||
| 15-May-23 | 6 | ||||
| 22-May-23 | 6 | ||||
| 29-May-23 | 6 |
I would need help on writing dax formula which would work on seperate connected calendar table to the employee table and this has to give me the count of the employee who has joinined at differnt days based on the weekly calendar date.
Other output is working day between the each stages such as joining date and r1 start date and so on. The working day example has not been included in the output table
KiranKJ , You have to create a common date table and join all dates with the date of date table. Other than one join all joins will be inactive
Use userelationship in measure to activate that
Power BI: HR Analytics - Employees as on Date : https://youtu.be/e6Y-l_JtCq4
https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-trend/ba-p/882970
1 Reply
KiranKJ , You have to create a common date table and join all dates with the date of date table. Other than one join all joins will be inactive
Use userelationship in measure to activate that
Power BI: HR Analytics - Employees as on Date : https://youtu.be/e6Y-l_JtCq4
https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-trend/ba-p/882970