Time duration calculation based on rows with unique identifiers

[Anonymous]

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

[gooranga1 · Accepted solution]

Hi @Anonymous

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.

[Anonymous]

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

[Anonymous]

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)

[gooranga1]

@Anonymous,

or if you want one measure you can just use,

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

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

[Anonymous]

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

[Anonymous]

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

One thing I noticed is that if I use the example from @Anonymous 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

[Anonymous]

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.