Total running time of scantimes

[Stewwe]

Hello,

I hope you can help me 🙂

First, I have the following table:

Trackingcode	Scantime
123456	26.11.2020 18:28
123456	27.11.2020 20:15
123456	27.11.2020 01:02
123456	26.11.2020 19:10
123456	25.11.2020 00:00
987654	30.11.2020 07:00
987654	30.11.2020 09:17
987654	30.11.2020 07:05
987654	30.11.2020 11:16
987654	30.11.2020 09:17

Now I want to get the highest and the lowest scantime for each trackingcode. After that I want to subtract each value to get the indivual runningtime for each tracking code.  At last I want to have a Measure "totalrunningtime" that sums everything up:
123456 = 27.11.2020 20:15 -  26.11.2020 18:28  = x
987654 = 30.11.2020 11:16 - 30.11.2020 07:05 =y

totalruninngtime=sum(x+y)

Puh, I hope you get the point 🙂

At best would be one measure that calculates everything in one step.

Any help kindly appreciated!

Thank you
Stewwe

[parry2k]

@Stewwe try following measure:

Time Difference = 
VAR __table = ALLEXCEPT ( Table, Table[TrackingCode] ) 
VAR __minTime = CALCULATE ( MIN ( Table[ScanTime] ), __table )
VAR __maxTime = CALCULATE ( MAX ( Table[ScanTime] ), __table )
RETURN
DATEDIFF ( __maxTime, __minTime, MINUTE )

Check my latest blog post Improve UX: Show Year in Legend When Using Time Intelligence Measures | PeryTUS IT Solutions  I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[Stewwe]

Hello @parry2k  thank you very much for your effort!
But unfortunately this doesn´t solve my problem completely, because if I sum up the min- and the max values it doesn´t match with the shown total sum 😕 

I tried:

Time Difference = 
VAR __table = ALLEXCEPT ( Table, Table[TrackingCode] ) 
VAR __minTime = CALCULATE ( MIN ( Table[ScanTime] ), __table )
VAR __maxTime = CALCULATE ( MAX ( Table[ScanTime] ), __table )
Var Diff = DATEDIFF ( __maxTime, __minTime, MINUTE )
Return
sumx(values(Table[TrackingCode]),Datediff)

But this shows a much higher Total 😕

Thank you

Stewwe

[Stewwe · Accepted solution]

Ok, now I have it!:

Time Difference = 
Time Difference =
VAR __table =
    ALLEXCEPT ( Table, Table[TrackingCode] )
RETURN
    SUMX (
        VALUES ( Table[TrackingCode] ),
        DATEDIFF (
            CALCULATE ( MAX ( Table[ScanTime] ), __table ),
            CALCULATE ( MIN ( Table[ScanTime] ), __table ),
            MINUTE
        )
    )

 Thank you!

Stewwe