Forum Discussion

Saad_Hanif456's avatar
Saad_Hanif456
Helper I
2 years ago
Solved

New Joiner 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 ), result as follows:

 

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

 

  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi Saad_Hanif456 ,

    Try this.

    Measure = 
VAR _a = MIN('Calendar'[Date])
var _b = EOMONTH(_a,0)
RETURN 
CALCULATE(
    DISTINCTCOUNT(Payroll[CNIC ]),
    FILTER(ALL(Payroll),[Firsthiringdate] >= _a && 'Payroll'[Firsthiringdate] <= _b)
)

     

     

  • Saad_Hanif456's avatar
    Saad_Hanif456
    2 years ago

    Dear Anonymous ,

    Perfect brother.

     

    Thank you so much.

     

    Regards,

    Saad Hanif

11 Replies

  • Saad_Hanif456's avatar
    Saad_Hanif456
    Helper I
    2 years ago

    Dear Power BI Experts,

     

    Please help me in this regards, I am stuck in this scenario.

     

    Regards,

    Saad Hanif

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi Saad_Hanif456 ,

    Regarding your question, I checked the data. Did you miss 'Arbaas'? He should be counted as a new employee in February, right?

    Please follow these steps:

    1.Delete the relationship between tables. It affects filtering.

    2.Use the following DAX expression to create a measure

    Measure = 
VAR _a = MIN('Calendar'[Date])
var _b = EOMONTH(_a,0)
RETURN 
CALCULATE(
    DISTINCTCOUNT(Payroll[CNIC ]),
    FILTER(
        Payroll,
        Payroll[firsthiringdate] >= _a && 
        Payroll[firsthiringdate] <= _b))

    3.Final output

    Best Regards,
    Wenbin Zhou
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • Saad_Hanif456's avatar
      Saad_Hanif456
      Helper I
      2 years ago

      Dear Anonymous ,

       

      Thank you so much for the help but i need the relationship in Calendar [Date] with Payroll [Payroll Month] active so that my other DAX would not get impacted..

      Yes, you are right, I missed Mr. Arbass, he should be reflected in month of Feb 2024

      Do you have any other idea with active relationship?

      Regards,

      Saad Hanif

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        2 years ago

        Hi Saad_Hanif456 ,

        You can copy your date table.

        Table = VALUES('Calendar')