Forum Discussion

jeags's avatar
jeags
Frequent Visitor
5 years ago

Multiple date columns - Employee Turnover Calculation - pbix attached

I am having trouble writing DAX that incorporates two different date columns. My pbix file can be found here.    I have two tables: 1. KAT: A scrubbed table of employees with Employee ID, Hire Dat...
Need Help
lbendlin's avatar
lbendlin
Icon for Super User rankSuper User
5 years ago

yes, you need to modify all measures. How else will they know which relationship to use?

 

Your last code example can be simplified quite a bit

 

Headcount =
CALCULATE (
    COUNTROWS ( 'KAT' ),
    'KAT'[Hire Date] <= MAX ( 'Date'[Date] ) ,
    COALESCE( 'KAT'[Termination Date], MIN ( 'Date'[Date] )) >= MIN ( 'Date'[Date] ),
)

 

but it would still be wrong as your MAX and MIN calculations are impacted by the CALCULATE context change. Use variables.

  • jeags's avatar
    jeags
    Frequent Visitor
    5 years ago

    Thank you, can you share what the calculation would look like with variables?

     

    And do you know how I would do the monthly and year-to-date turnover measures?

    For example:

    - Monthly turnover Jan 2020 = number of terminations in January 2020/January 2020 headcount

    - Year to date turnover in March 2020 = (# of terms in Jan + # of terms in Feb + # of terms in Mar)/(Jan headcount + Feb headcount+ March headcount)/3