Forum Discussion

tarmogolf's avatar
tarmogolf
Advocate I
4 years ago
Solved

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)
Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    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 

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    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