Forum Discussion
New Joiners Count
Dear Experts,
Need to calculate how many employees joined each month but the trick is in the following table example: (Remarks column is given for help)
| S.No | Payroll Month | Code | Name | CNIC | Hiring Date | Remarks |
| 1 | 1-Jan-24 | 10438 | Imtiaz | 45555887771 | 1-Jan-24 | Hired |
| 2 | 1-Jan-24 | 10320 | Safdar | 45555887772 | 1-Feb-23 | |
| 3 | 1-Jan-24 | 10553 | Ali | 45555887773 | 1-Oct-23 | |
| 4 | 1-Jan-24 | 10042 | Hussain | 45555887774 | 1-Jan-24 | HIred |
| 5 | 1-Feb-24 | 10438 | Imtiaz | 45555887771 | 1-Jan-24 | |
| 6 | 1-Feb-24 | 10320 | Safdar | 45555887772 | 1-Feb-23 | |
| 7 | 1-Feb-24 | 10553 | Ali | 45555887773 | 1-Oct-23 | |
| 8 | 1-Feb-24 | 10042 | Hussain | 45555887774 | 1-Jan-24 | |
| 9 | 1-Feb-24 | 10953 | Waseem | 45555887776 | 1-Feb-24 | Hired |
| 10 | 1-Feb-24 | 10993 | khurram | 45555887777 | 1-Feb-24 | hired |
| 11 | 1-Mar-24 | 10555 | Imtiaz | 45555887771 | 1-Mar-24 | Confirmed with new Emp ID, CNIC is same |
| 12 | 1-Mar-24 | 10556 | Hussain | 45555887774 | 1-Mar-24 | Confirmed with new Emp ID, CNIC is same |
| 13 | 1-Mar-24 | 10399 | Arbaas | 45555887775 | 1-Feb-24 | |
| 14 | 1-Mar-24 | 10993 | khurram | 45555887777 | 1-Feb-24 | |
| 15 | 1-Mar-24 | 10959 | maheen | 45555887780 | 1-Mar-24 | Hired |
| 16 | 1-Apr-24 | 10555 | Imtiaz | 45555887771 | 1-Mar-24 | |
| 17 | 1-Apr-24 | 10556 | Hussain | 45555887774 | 1-Mar-24 | |
| 18 | 1-Apr-24 | 10959 | maheen | 45555887780 | 1-Mar-24 | |
| 19 | 1-Apr-24 | 10541 | Idrees | 45555887781 | 25-Mar-24 | He was hired in March 2024 but was included in April 2024 payroll. I want him to be counted in the month of March 2024 only as per hiring date |
| 20 | 1-Apr-24 | 10557 | Waseem | 45555887776 | 1-Apr-24 | Confirmed with new Emp ID |
| 21 | 1-Apr-24 | 10558 | khurram | 45555887777 | 1-Apr-24 | Confirmed with new Emp ID |
1- Imtiaz and safdar joined in Jan 2024 and confirmed in Mar 2024 with a new emp ID, CNIC is the same. I do not want to count them in March 2024 because they were already counted in Jan 2024. (That i have already done with DAX with the following)
Step 1: Add a calculated column to determine the first hiring date for each CNIC:
firsthiringdate =
CALCULATE(
MIN(Payroll[Hiring Date]),
ALLEXCEPT(Payroll,Payroll[CNIC]))
Step 2: Create a measure to count the number of unique employees who joined in each month based on this first hiring date
VAR CurrentMonth = SELECTEDVALUE(Payroll[Payroll Month])
RETURN
CALCULATE(
DISTINCTCOUNT(Payroll[CNIC]),
FILTER(
Payroll,
Payroll[firsthiringdate] >= EOMONTH(CurrentMonth, -1) +1 &&
Payroll[firsthiringdate] <= EOMONTH(CurrentMonth,1)))
| Months | Result |
| Jan | 2 |
| Feb | 2 |
| Mar | 1 |
| Apr | 0 |
2 - However, Mr. idrees who was joined in March 2024 is not being counted since he has been added in the Payroll of April 2024
| Months | Result | Required |
| Jan | 2 | 2 |
| Feb | 2 | 2 |
| Mar | 1 | 2 |
| Apr | 0 | 0 |
Please help in this regards and suggest the DAX where my both 1 and 2 condition gets fulfilled so that i can move forward quickly.
PBX FIle is enclosed for working (https://drive.google.com/file/d/1N7qHOwNoO9nsP_bkmLxt4keIBanrcxAA/view?usp=sharing )
Regards
Saad Hanif
Dear Rupak_bi,
I wanted the result same as in the following table visual:
Note: Have the silcer of calendar[month], calender[year] and department [name] on the canvasMonth Name Hire Jan Imtiaz 1 Jan Hussain 1 Feb Waseem 1 Feb Khurram 1 Feb Arbaas 1 Mar Maheen 1 Mar Idress 1 Regards,
Saad Hanif
5 Replies
Hi I think below will be your expected out based on data. Feb count will be 3 , Khurram, Arbaas, Waseem
Dear Rupak_bi,
I wanted the result same as in the following table visual:
Note: Have the silcer of calendar[month], calender[year] and department [name] on the canvasMonth Name Hire Jan Imtiaz 1 Jan Hussain 1 Feb Waseem 1 Feb Khurram 1 Feb Arbaas 1 Mar Maheen 1 Mar Idress 1 Regards,
Saad Hanif
just update the table relationship to "firsthiredate"
If this solves your problem, Plz accept it as solution.
