Calculate number of working days between slicer selection and factor in Start / Term dates

[BDale93]

Hello all,

I'm needing some advice on the best way to calculate the number of working days for a users between the selected slicer date range. I need to consider the employees potential employement start / termination dates as well. If a user start and or term date falls between the selected date range, then calculate only the days after the start date and or before the term date, else count all the days between the slicer range

Example 1:

Slicer Range = July 1st thru September 31st
User Start Date = July 27th
Result should be the number of days between July 27th and September 31st

Example 2:

Slicer Range = July 1st thru September 31st
User Term Date = July 27th
Result should be the number of days between July 1st and July 27th

Example 3:

Slicer Range = July 1st thru September 31st
User Start Date = July 27th
User Term Date = September 15th
Result should be the number of days between July 27th and September 15th

Any help is appreciated!

[CoreyP · Accepted solution]

Something like this should work for you. Just make sure your date table is disconnected from your employee table.

[BDale93]

CoreyP,

This is great, thank you. One last piece to this which I didnt add into the Examples and current logic is not working. The way my report is set up is the month slicer that needs to be used summarizes the entire month. So 7/1/2023 is the entire month, not just the July 1st. Therefore if a consumer selects 7/1/2023 instead of a range the same logic needs to apply as it would a entire date range. Thats not the case, only works for termed employees during that month. I've tried using an additional variable for individual month selection (

VAR SlicerValue = SELECTEDVALUE('Date Table'[Month])) but cannot seem to get the logic to work. Any additional help would be aprpeciated.

[CoreyP]

I'm not sure I understand what you mean. When I use a month slicer, it still works. The date range when a month is selected is from the 1st to the end of the month.

[BDale93]

I had made a mistake and was using 'Month' field when creating the slicer min / max. Switched to 'Date' and it's now working perfectly. Thanks again