DATEDIFF Duration between Start and End with Slicer Context

jaws2k — Mon, 04 May 2020 10:11:12 GMT

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:

A	4299934
B	7176474
C	5867059

Can anyone help me out with this?

Re: DATEDIFF Duration between Start and End with Slicer Context

Greg_Deckler — Mon, 04 May 2020 11:17:55 GMT

So, it seems like you need a variation of Open Tickets. https://community.powerbi.com/t5/Quick-Measures-Gallery/Open-Tickets/m-p/409364#M147

Re: DATEDIFF Duration between Start and End with Slicer Context

Anonymous — Mon, 04 May 2020 12:33:01 GMT

Your calculations don't seem to be correct. Please see the attached file.

Best

topic Re: DATEDIFF Duration between Start and End with Slicer Context in DAX Commands and Tips

DATEDIFF Duration between Start and End with Slicer Context

Re: DATEDIFF Duration between Start and End with Slicer Context

Re: DATEDIFF Duration between Start and End with Slicer Context