Reply
Frequent Visitor
Posts: 3
Registered: ‎06-11-2018
Accepted Solution

Time duration calculation based on rows with unique identifiers

Hi all, been searching the forums for an answer for a calculation I need and so far have come up blank... 

 

I've looked through the following threads (as these seem to have solved a similar 'problem') but the solutions don't appear to work for me - or I can't get them to work correctly?

 

https://community.powerbi.com/t5/Desktop/Calculate-duration-based-on-dates-in-different-rows/m-p/588...

https://community.powerbi.com/t5/Desktop/DATEDIFF-between-Rows-and-GROUP-BY/td-p/309994 - this is seemingly a very similar problem as I'm facing and while the measure works, the values I get are not as expected. 

 

Essentially, I have rows which are distinguished by a unique id "SessionID" and the number of rows for each SessionID isn't fixed. 

 

I'm trying to formulate a DATEDIFF(?) output which looks at the highest and lowest value in the date/time column for each distinct SessionID and returns the difference. So for SessionID 2 below, I'd like an output returned of 7 secs - for SessionID 3 > 204 secs and so on... 

 

I'm new to DAX and each time I think I'm close with the expression I need, it just doesn't work... please send help! 

 

SessionID

Audit Date

2

06/09/2017 13:46:14

2

06/09/2017 13:46:20

2

06/09/2017 13:46:20

2

06/09/2017 13:46:21

3

06/09/2017 13:49:01

3

06/09/2017 13:51:10

3

06/09/2017 13:52:00

3

06/09/2017 13:52:24

3

06/09/2017 13:52:25

4

06/09/2017 13:54:26

4

06/09/2017 13:54:32

4

06/09/2017 13:54:36

4

06/09/2017 13:54:36

4

06/09/2017 13:54:36

4

06/09/2017 13:54:57

4

06/09/2017 13:55:06

4

06/09/2017 13:55:06

5

06/09/2017 13:55:23

5

06/09/2017 13:55:30

5

06/09/2017 13:55:50

5

06/09/2017 13:55:52

5

06/09/2017 13:56:09

5

06/09/2017 13:56:09

5

06/09/2017 13:56:10

5

06/09/2017 13:56:11

5

06/09/2017 13:56:11

 

Thanks in advance,

Daniel


Accepted Solutions
Established Member
Posts: 255
Registered: ‎03-31-2016

Re: Time duration calculation based on rows with unique identifiers

Hi @danielgilbey

 

If you create 2 measures that max and min the Audit Date. then create a 3rd measure that does a datediff of these 2 measures.

 

MinAudit = CALCULATE(min(Table10[Audit Date]))

MaxAudit = CALCULATE(max(Table10[Audit Date]))

Difference = DATEDIFF([MinAudit], [MAxAudit],SECOND)

Then add them to a matrix with SessionId on Rows it should give you what you need.

 

CaptureMinMax.PNG

View solution in original post


All Replies
Regular Visitor
Posts: 29
Registered: ‎03-02-2018

Re: Time duration calculation based on rows with unique identifiers

I have not done this specifically, but my approach would be to use MINX with a Filter on the SessionID.. Something like the following.. 

 

MINX(KEEPFILTERS(VALUES('Data'[WW])), CALCULATE(MIN('Data'[Date])))

 

 You could then do the same with MAXX and use the output of those to in order to get the Time Difference.

Established Member
Posts: 255
Registered: ‎03-31-2016

Re: Time duration calculation based on rows with unique identifiers

Hi @danielgilbey

 

If you create 2 measures that max and min the Audit Date. then create a 3rd measure that does a datediff of these 2 measures.

 

MinAudit = CALCULATE(min(Table10[Audit Date]))

MaxAudit = CALCULATE(max(Table10[Audit Date]))

Difference = DATEDIFF([MinAudit], [MAxAudit],SECOND)

Then add them to a matrix with SessionId on Rows it should give you what you need.

 

CaptureMinMax.PNG

Frequent Visitor
Posts: 3
Registered: ‎06-11-2018

Re: Time duration calculation based on rows with unique identifiers

Hi @gooranga1 thanks for this, this did indeed work. Would you (or anyone) know of a way of combining the measures to create a single measure? Rather than having 3 'bouncing' off of each other...

Either way, this does what I need for now - would welcome any alternative solutions anyone may have.

 

ATB, Daniel

Highlighted
Regular Visitor
Posts: 29
Registered: ‎03-02-2018

Re: Time duration calculation based on rows with unique identifiers

[ Edited ]

If using @gooranga1 method, just create the MinAudit and MaxAudit as a variable

var MinAudit = CALCULATE(min(Table10[Audit Date]))

var MaxAudit = CALCULATE(max(Table10[Audit Date]))

RETURN Difference = DATEDIFF(MinAudit, MaxAudit,SECOND)

 

Established Member
Posts: 255
Registered: ‎03-31-2016

Re: Time duration calculation based on rows with unique identifiers

@danielgilbey,

 

or if you want one measure you can just use,

 

Difference 1 = DATEDIFF(CALCULATE(min(Table10[Audit Date])), CALCULATE(max(Table10[Audit Date])),SECOND)

measure with 1.PNG

 

@bccolema how would your method be used inside power bi desktop? Where does that code get pasted?

Regular Visitor
Posts: 29
Registered: ‎03-02-2018

Re: Time duration calculation based on rows with unique identifiers

@gooranga1  All in the DAX expression just like you are doing there, but instead

 

Difference 1 =  
var MinAudit = CALCULATE(min(Table10[Audit Date])) var MaxAudit = CALCULATE(max(Table10[Audit Date]))
RETURN Difference = DATEDIFF(MinAudit, MaxAudit,SECOND)

 

Frequent Visitor
Posts: 3
Registered: ‎06-11-2018

Re: Time duration calculation based on rows with unique identifiers

[ Edited ]

Thanks (again) to both @bccolema and @gooranga1 for the additional responses! Greatly appreciated!

 

One thing I noticed is that if I use the example from @bccolema I have to remove (after RETURN) " Difference = " as this seems to break the expression. 

 

So I end up with the below (for anyone else looking)

 

 

Time in seconds =
var MINaudit = CALCULATE(min('tablename'[AuditDate]))
var MAXaudit = CALCULATE(max('tablename'[AuditDate]))

RETURN
DATEDIFF(MinAudit, MaxAudit,SECOND)

 

ATB, Daniel