cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
jaws2k
Frequent Visitor

DATEDIFF Duration between Start and End with Slicer Context

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.

 

GroupStartDateEndDateDuration
A1-Feb-2020 0:35:1514-Feb-2020 23:37:233015319
B1-Jan-2020 17:23:4730-Mar-2020 5:47:1019119507
C15-Feb-2020 11:53:4215-Mar-2020 4:10:096194467
A9-Feb-2020 0:53:1714-Feb-2020 23:37:231284615
B10-Jan-2020 17:43:575-Feb-2020 5:23:115504884
C17-Feb-2020 8:12:325-Mar-2020 14:12:293725992

 

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:

A4299934
B24624391
C9920459

 

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:

GroupFiltered_StartFiltered_EndFiltered Duration
A1-Feb-2020 0:35:1514-Feb-2020 23:37:233015319
B1-Feb-2020 0:00:0029-Feb-2020 23:59:596263997
C15-Feb-2020 11:53:4229-Feb-2020 23:59:593132942
A9-Feb-2020 0:53:1714-Feb-2020 23:37:231284615
B1-Feb-2020 0:00:005-Feb-2020 5:23:11912477
C17-Feb-2020 8:12:3229-Feb-2020 23:59:592734117

 

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:

A4299934
B7176474
C5867059

 

Can anyone help me out with this?

2 REPLIES 2
Anonymous
Not applicable

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

 

Best

D

Greg_Deckler
Super User IV
Super User IV

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


---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Get Ready for Power BI Dev Camp

Power BI Dev Camp - June 24th

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors