cancel
Showing results for
Did you mean:
Frequent Visitor

## Date Difference on each row

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)
1 ACCEPTED SOLUTION
Community Support

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

Community Support Team _ Jay
If this post helps, then please consider Accept it as the solution
to help the other members find it.
Community Support

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

Community Support Team _ Jay
If this post helps, then please consider Accept it as the solution
to help the other members find it.