Forum Discussion

Saad_Hanif456's avatar
Saad_Hanif456
Helper I
2 years ago
Solved

New Hires Count

Dear Experts,

 

I need to calculate the count New Hires month wise as per following condition through DAX Calculation which is also mentioned in the sample Working Sheet.

 

1. There are two Employees Mr. Imtiaz and Mr. Hussain who have joined in Jan 01, 2023 but upon confirmation we generate New Code and new Date of Joining of the same month that is April 01, 2023. Their Date of Joining should be counted on Jan 01, 2023. We can calculate the count through CNIC column as well since the CNIC number is the same.

 

2. I have slicer of Calendar [ Year ] and Calendar [ Month ].

 

Link of the files mentioned below for reference.

https://drive.google.com/file/d/1f2A6KLuCjnq05UxS0xB8Li7XD-E9SqgG/view?usp=sharing

 

Result Required
MonthsNew joiners
Jan2
Feb3
Mar2
Apr3


Regards,

Saad Hanif

 

  • VillyMBI's avatar
    VillyMBI
    2 years ago

    Hello Saad_Hanif456 ,

    You have not specified anything about relationship between 'Calendar' and 'Payroll' table.
    Considering you have an active relationship with 'Hiring date'
    Head Count =DISTINCTCOUNT([CNIC ])


    If not, considering you have an active relationship with 'Payroll Month' and a inactive relationship with 'Hiring date'

    Head Count = CALCULATE(DISTINCTCOUNT([CNIC ]), USERELATIONSHIP(Payroll[Hiring Date],'Calendar'[Date]))

     
     
    I hope this will help you 🙂
  • VillyMBI's avatar
    VillyMBI
    2 years ago

    Hello Saad_Hanif456 ,

     

    Please try below DAX

    Head Count =
    VAR _selectedMonthYear = FORMAT(MAX('Calendar'[Date]),"YYYY-MM")
    VAR _table = SUMMARIZE(ALL(Payroll), [CNIC ], "HDdate", FORMAT(MIN([Hiring Date]), "YYYY-MM"))
    RETURN COUNTROWS(FILTER(_table, [HDdate] = _selectedMonthYear))

    I hope this will help you 🙂

5 Replies

  • VillyMBI's avatar
    VillyMBI
    Resolver I
    2 years ago

    Hello Saad_Hanif456 ,

    You have not specified anything about relationship between 'Calendar' and 'Payroll' table.
    Considering you have an active relationship with 'Hiring date'
    Head Count =DISTINCTCOUNT([CNIC ])


    If not, considering you have an active relationship with 'Payroll Month' and a inactive relationship with 'Hiring date'

    Head Count = CALCULATE(DISTINCTCOUNT([CNIC ]), USERELATIONSHIP(Payroll[Hiring Date],'Calendar'[Date]))

     
     
    I hope this will help you 🙂
  • Saad_Hanif456's avatar
    Saad_Hanif456
    Helper I
    2 years ago

    Dear VillyMBI,

    Thank you so much for the response. 

    Relationship is with Calendar [date] and Payroll Month [Date]

    The result is not coming up what is required. I have got the hire count by your Dax suggestion but the employee the employee named Mr. Imtiaz hired in Jan 01, 2023. he is also reflecting in the month of April 2023 since his data of joining has been changed upon confirmation and I want his headcount comes onlyon the first hiring date i.e. Jan 01, 2023 when he was first hired

     

    Please add your input, if you have got the understanding of it.

     

    Regards,

    Saad Hanif

     

      • Saad_Hanif456's avatar
        Saad_Hanif456
        Helper I
        2 years ago

        Once again thank you VillyMBI. I have got the New Hires Count but did not get the result that is required.

        This Dax Expression Count the same Mr. Imtiaz name twice in Jan 01, 2023 and April 01, 2023 whereas i want his count reflected when he appointed first that is Jan 01, 2023.