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

You say you have two inactive relationships. That means you have to specify the USERELATIONSHIP() modifier all the time, in all your measures.

 

Usually the more dominant relationship is kept active, which saves some typing.

 

How are your users specifying the date intervals?  Or are these immutable?

jeags's avatar
jeags
Frequent Visitor
5 years ago

Does that mean I need to also modify the above Headcount measure?

 

The date intervals are either monthly, year-to-date, or year-to-date of the previous year. The users will want to know the number of hires, number of terminations, and the (average) headcount for each of those intervals.

 

Hope that helps clarify!

 

Headcount =
CALCULATE (
    COUNTROWS ( 'KAT' ),
    FILTER ( VALUES ( 'KAT'[Hire Date] ), 'KAT'[Hire Date] <= MAX ( 'Date'[Date] ) ),
    FILTER (
        VALUES ( 'KAT'[Termination Date] ),
        OR (
            'KAT'[Termination Date] >= MIN ( 'Date'[Date] ),
            ISBLANK ( 'KAT'[Termination Date] )
        )
    )
)