Date Difference on each row

[tarmogolf]

Happy Friday, and thank you in advance for any assistance you can give. I currently have the measure below to count the number of employees that were active during a selected period, and I need to modify it to calculate how many months during the selected period they were active. I need this to calculate each individual employee, and then roll up through the hierarchy (Employee > Location > Market).

Each employee has a [Termination Date] and a [Hire Date]. My thought was to do a DATEDIFF to calculate this for each employee, but I'm unsure how to accomplish it, since I cannot just use the selected date range for the DATEDIFF parameters - if the employee was hired during the selected date range then their [Hire Date] should be used in place of the minimum selected date, same thing for [Termination Date] and max date. 

Measure = 

var minDate = MIN('Date'[Date])

var maxDate = IF(MAX('Date'[Date])> Today(), TODAY(), MAX('Date'[Date]))

return

CALCULATE(

COUNTROWS('Employee Roster'),

'Employee Roster'[StartDate] <= maxDate,

'Employee Roster'[TerminationDate] = BLANK() || 'Employee Roster'[TerminationDate] > minDate)

[Anonymous · Accepted solution]

Hi @tarmogolf ,

Create a date slicer table and use Min() to get the range start and use Max() to get range end.

Then create a measure like below:

meaasure =
VAR _start =
    IF (
        SELECTEDVALUE ( 'Employee Roster'[StartDate] ) > MIN ( 'Date'[Date] ),
        SELECTEDVALUE ( 'Employee Roster'[StartDate] ),
        MIN ( 'Date'[Date] )
    )
VAR _end =
    IF (
        SELECTEDVALUE ( 'Employee Roster'[TerminationDate] ) < MAX ( 'Date'[Date] ),
        SELECTEDVALUE ( 'Employee Roster'[TerminationDate] ),
        MAX ( 'Date'[Date] )
    )
RETURN
    DATEDIFF ( _start, _end, DAY )

Best Regards,

Jay