HI,
I've been trying to work this out, but cant seem to get the DAX right.
I have a table with some start and end times for outages, and I want to measure the duration. Sometimes, the duration can go over days, weeks or months.
I want to calculate the sum of the outages for a particular group, but to show this in the context of a date filter.
While I have a calculated column calculating the Duration, this only gives me total duration. Sample Data Below.
| Group | StartDate | EndDate | Duration |
| A | 1-Feb-2020 0:35:15 | 14-Feb-2020 23:37:23 | 3015319 |
| B | 1-Jan-2020 17:23:47 | 30-Mar-2020 5:47:10 | 19119507 |
| C | 15-Feb-2020 11:53:42 | 15-Mar-2020 4:10:09 | 6194467 |
| A | 9-Feb-2020 0:53:17 | 14-Feb-2020 23:37:23 | 1284615 |
| B | 10-Jan-2020 17:43:57 | 5-Feb-2020 5:23:11 | 5504884 |
| C | 17-Feb-2020 8:12:32 | 5-Mar-2020 14:12:29 | 3725992 |
In the above, Duration is a calculated column with the formula:
Duration = DATEDIFF(StartDate, EndDate, SECOND)
A sum of the Duration by group therefore would be:
| A | 4299934 |
| B | 24624391 |
| C | 9920459 |
However, If I had a date filter to filter the month of February 2020, I want to show the outage for that period.
Using the above data, I want the effective times to be:
| Group | Filtered_Start | Filtered_End | Filtered Duration |
| A | 1-Feb-2020 0:35:15 | 14-Feb-2020 23:37:23 | 3015319 |
| B | 1-Feb-2020 0:00:00 | 29-Feb-2020 23:59:59 | 6263997 |
| C | 15-Feb-2020 11:53:42 | 29-Feb-2020 23:59:59 | 3132942 |
| A | 9-Feb-2020 0:53:17 | 14-Feb-2020 23:37:23 | 1284615 |
| B | 1-Feb-2020 0:00:00 | 5-Feb-2020 5:23:11 | 912477 |
| C | 17-Feb-2020 8:12:32 | 29-Feb-2020 23:59:59 | 2734117 |
As an example, I have calculated the above as:
var filter_mindate = MIN(DateTable[Date])
var filter_maxdate = MAX(DateTable[Date])
Filtered_Start = MAX(filter_mindate, StartDate)
Filtered_End = MIN(filter_maxdate, EndDate)
Filtered_Duration = DATEDIFF(Filtered_Start, Filtered_End, SECOND)
...and the outcome to be:
Can anyone help me out with this?
