Forum Discussion

Saad_Hanif456's avatar
Saad_Hanif456
Icon for Helper I rankHelper 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 Emplo...
  • 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 🙂
Saad_Hanif456's avatar
Saad_Hanif456
Icon for Helper I rankHelper 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.

VillyMBI's avatar
VillyMBI
Icon for Resolver I rankResolver I
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 🙂