Dynamic duration between two date columns and two slicer dates

[Anonymous]

Hey all,

I am working with Power Bi for quite some time now and have used answers on the forum pretty often to help perfect my measures. But now I am running into something I just can't seem to work out and I would like a fresh view of the matter and hopefully some help in solving the problem. I have tried solving the problem with DAX and would prefer that, but if Power Query/M is needed that would also be an option (or even if I have to solve it before pushing the data to Power Bi). It might be a simple solution, and I might be completely on the wrong track as well, so any input on the matter is very welcome even if it is a complete other direction! I just seem to be stuck in my thinking at the moment, can't think of anything else. Many thanks in advance!

Below I have put a print screen of a sample of data I am trying to work with and the DAX I have come up with so far. I have data that include various date columns, but the ones I am focussing on here are the date where a co-worker has called in sick (column 'Ziek') and the date the same co-worker announced to be able to go back to work (column 'Beter'). I need to be able to calculate the total number of sick-days for each co-worker, but with the extra challenge of a date slicer with start and end date that might cut off the total duration from 'Ziek' to 'Beter'. 

So in the example below my date slicer starts on 1-2-2020 and for all co-workers with a sick-date before that, the count of sick-days needs to start on 1-2-2020 (so for example row two 15-1-2020, needs to not count the days from 15-1 to 1-2, but only the days from 1-2 to 15-2). And my date slicer stops on 1-4-2020, so everything 'Beter' after should also not be considered. In the column 'Aantal' you can find the total number of days I expect to find as duration between the date columns and my slicer range. 

My desired outcome would be a sum of all days for all co-workers that have sick-days within my slicer range (in this example 120). I have been able to create a table with all correct values for each row when someone only shows up once (that also filters down to 'Bedrijf' and than only shows the rows belonging to that one), but I can't seem to find the right measure to sum all individual values.

And also, as soon as I add a recurring co-worker with another date range within my slicer range (for example row 2/mdw b is ill from 15-1-2020 to 15-2-2020 AND than I add a new row (row 4) for mdw b from 15-3-2020 to 15-4-2020), it only shows me 14 for mdw b, not 17 as well for the second date range and also not 31 as a total between the two date ranges (see second print screen below). 

Is there anyone that can help me along with this? Again, many thanks in advance!

[Anonymous]

In the mean time I could not stop thinking about this and the solution is indeed a very simple one that I was completely overlooking! I had an active relationship between the date table used in my slicer and one of my date columns in my dataset. So @mahoneypat, your solution really works like a charm, thanks for your help!

[Anonymous]

I have tried the exact approach you suggested (and some variations, including if-statements), but for some reason the measure works perfectly on the sample data as provided above, but as soon as I try it out on my real dataset it doesn't work. What it seems to be doing is only sum the days of my data where the start date is greater than the start date of the slicer, but as soon as the start data falls before the start date of the slicer, it ignores those rows. 

Edit: removed part of the post, as it has no added value to the conversation and solution.

[mahoneypat · Accepted solution]

Please try an approach like this

Sick Days in Range = var vMinSlicer = MIN('Date'[Date])

var vMaxSlicer = MAX('Date'[Date])

var vResult = SUMX(T1, var vSick = T1[Sick]

var vBetter = T1[Better]

var vNewMin = MAX(vMinSlicer, vSick)

var vNewMax = MIN(vMaxSlicer, vBetter)

return DATEDIFF(vNewMin, vNewMax, DAY))

return vResult

Pat