- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

# Time duration calculation based on rows with unique identifiers

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

06-11-2018 05:45 AM

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/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

Solved! Go to Solution.

Accepted Solutions

## Re: Time duration calculation based on rows with unique identifiers

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

06-11-2018 06:24 AM

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.

All Replies

## Re: Time duration calculation based on rows with unique identifiers

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

06-11-2018 06:00 AM

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.

## Re: Time duration calculation based on rows with unique identifiers

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

06-11-2018 06:24 AM

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.

## Re: Time duration calculation based on rows with unique identifiers

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

06-19-2018 06:39 AM

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

## Re: Time duration calculation based on rows with unique identifiers

[ Edited ]- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

06-19-2018 07:27 AM - edited 06-19-2018 07:28 AM

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)

## Re: Time duration calculation based on rows with unique identifiers

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

06-19-2018 07:55 AM

or if you want one measure you can just use,

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

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

## Re: Time duration calculation based on rows with unique identifiers

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

06-19-2018 09:17 AM

@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)

## Re: Time duration calculation based on rows with unique identifiers

[ Edited ]- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

06-21-2018 07:01 AM - edited 06-21-2018 07:19 AM

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