cancel
Showing results for
Did you mean:

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a \$100 discount. Register Now

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.

Announcements

#### Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

#### Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

#### Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

#### Power BI Header Update

Read about the exciting updates for the Power BI forum.

Top Solution Authors
Top Kudoed Authors