Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

How to find current year joiners and current years leavers using DAX

Hello experts ,

 

How to find current year joiners and current years leavers using DAX based on the count of emp id ..I want to display on KPI (one is current joiners and other one is current leavers ).

 

In below screen shot when i am selecting particular year for eg:(2007) it should be display current year joiners and current year leavers ,if i am not selecting any year from the slicer it also should be display value (joiners ,leavers) on KPI i mean here max current year 2019 vcurrent year value display ) 

 

in case if it is not possible to display on KPI atleast i want to display on card using DAX .please advise me or share me DAX 

 

 

I have table HRMS tables and calender table .

 

termd 0 means not terminated 1 means terminated 

 

 

 

 

 

 

 

Interesting Links
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • Anonymous's avatar
    Anonymous
    6 years ago

    Hello all ,

     

    I have a got answer my self please find below dax

     

    # joiners

    #joi =
    var p1 = CALCULATE(DISTINCTCOUNT(HRMS[EmpID]),YEAR('Calendar'[Date])=YEAR(TODAY()),FILTER(HRMS,HRMS[Termd] = 0))+0
    VAR _selectedYear = SELECTEDVALUE('Calendar'[Date].[Year])
    var p2 = CALCULATE(DISTINCTCOUNT(HRMS[EmpID]),FILTER(HRMS,HRMS[Termd] = 0))+0

    var result = IF(_selectedYear = 0 , p1,p2)
    return result
     
     
    #leavers
    ******
    #leav =
    var p1 =CALCULATE(DISTINCTCOUNT(HRMS[EmpID]),YEAR('Calendar'[Date])=YEAR(TODAY()),FILTER(HRMS,HRMS[Termd] = 1))+0
    VAR _selectedYear = SELECTEDVALUE('Calendar'[Date].[Year])
    var p2 = CALCULATE(DISTINCTCOUNT(HRMS[EmpID]),FILTER(HRMS,HRMS[Termd] = 1))+0

    var result = IF(_selectedYear = 0 , p1 ,p2)
    return result
     

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hello all ,

     

    I have a got answer my self please find below dax

     

    # joiners

    #joi =
    var p1 = CALCULATE(DISTINCTCOUNT(HRMS[EmpID]),YEAR('Calendar'[Date])=YEAR(TODAY()),FILTER(HRMS,HRMS[Termd] = 0))+0
    VAR _selectedYear = SELECTEDVALUE('Calendar'[Date].[Year])
    var p2 = CALCULATE(DISTINCTCOUNT(HRMS[EmpID]),FILTER(HRMS,HRMS[Termd] = 0))+0

    var result = IF(_selectedYear = 0 , p1,p2)
    return result
     
     
    #leavers
    ******
    #leav =
    var p1 =CALCULATE(DISTINCTCOUNT(HRMS[EmpID]),YEAR('Calendar'[Date])=YEAR(TODAY()),FILTER(HRMS,HRMS[Termd] = 1))+0
    VAR _selectedYear = SELECTEDVALUE('Calendar'[Date].[Year])
    var p2 = CALCULATE(DISTINCTCOUNT(HRMS[EmpID]),FILTER(HRMS,HRMS[Termd] = 1))+0

    var result = IF(_selectedYear = 0 , p1 ,p2)
    return result