Calculate for every different product

[Anonymous]

Hey!

I wanna calculate the RunTime of a device the last 24 hours, but I am having problem to make it filter just its devices as:

Device         Index           TotalRunTime (h)             (NewRunLoad)

A                    1                       2046                                 6

B                    2                       3                                       3 

A                    3                       2040                                 0 

B                    4                       0                                       0

(There are even more data)

 I want a column that calculates the different since the last index OF ITS OWN DEVICE.  For example for device A 2046-2040 = 6, I tried a couple of different functions but I cant really figure it out. 

NewRunLoad =
var ett = LOOKUPVALUE('Data'[RunLoad_h], Data[Index], Data[Index])
var tva = LOOKUPVALUE('Data'[RunLoad_h],'Data'[Index], Data[Index]-1)
return
CALCULATE(ett-tva, ALLEXCEPT(Data,Data[Device])

Kind regards

[LivioLanzo · Accepted solution]

@Anonymous

I think this should do what you want:

Column =
VAR minIndx =
    CALCULATE (
        MIN ( Table1[Index] ),
        ALLEXCEPT ( Table1, Table1[Device] ),
        Table1[Index] > EARLIER ( Table1[Index] )
    )
RETURN
    IF (
        ISBLANK ( minIndx ),
        BLANK (),
        Table1[TotalRunTime (h)]
            - CALCULATE (
                SUM ( Table1[TotalRunTime (h)] ),
                Table1[Index] = minIndx,
                ALL ( Table1 )
            )
    )