New Joiners Count

[Saad_Hanif456]

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

I want his count reflects in the month of March 2024 but the with aforemention DAX he is not refelecting in March 2024 neither in 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

[Rupak_bi]

Hi I think below will be your expected out based on data. Feb count will be 3 , Khurram, Arbaas, Waseem

[Saad_Hanif456 · Accepted solution]

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 canvas

Month	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

[Saad_Hanif456]

Dear @Rupak_bi ,

Thank you so much

[Rupak_bi]

Hi , Thanks for your reply. Please accept my last reply as solution so that other fellow can refer.

[Rupak_bi]

just update the table relationship to "firsthiredate"

If this solves your problem, Plz accept it as solution.