Subtract previous value from prior row where Ids are not continuous

[Anonymous]

Hi I am trying to find the engine hours for the following data set:

Currently I am using the following function to get the Engine_hrs :

Engine_hrs =

VAR PreviousRecords =

FILTER (

MachineHours,

MachineHours[id]

= EARLIER (MachineHours[id] ) - 1

&& MachineHours[MachineName] = EARLIEST ( MachineHours[MachineName] )

)

RETURN

IF (

COUNTROWS ( PreviousRecords ) > 0,

MachineHours[EngineRunningHours] - MAXX ( PreviousRecords, MachineHours[EngineRunningHours] )

)

However the problem is since the Ids are not continous it is failing to register for the discontinuous increments in Ids. Please do let me know if there is a possible way around this. 
Thank you!

[v-alq-msft]

Hi, @Anonymous 

I'd like to suggest you create a calcuated column with the following dax.

Engine_hrs =
VAR PreviousID =
    CALCULATE (
        MAX ( MachineHours[id] ),
        FILTER (
            MachineHours,
            MachineHours[id] < EARLIER ( MachineHours[id] )
                && MachineHours[MachineName] = EARLIER ( MachineHours[MachineName] )
        )
    )
VAR PreviousRecords =
    FILTER (
        MachineHours,
        MachineHours[id] = PreviousID
            && MachineHours[MachineName] = EARLIER ( MachineHours[MachineName] )
    )
RETURN
    IF (
        COUNTROWS ( PreviousRecords ) > 0,
        MachineHours[EngineRunningHours]
            - MAXX ( PreviousRecords, MachineHours[EngineRunningHours] )
    )

Best Regards

Allan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak · Accepted solution]

@Anonymous , Try a new column like

Engine_hrs =
VAR PreviousRecords =
MAXX(FILTER (
MachineHours,
MachineHours[id]
< EARLIER (MachineHours[id] )
&& MachineHours[MachineName] = EARLIEST ( MachineHours[MachineName] )
),MachineHours[id])
RETURN
IF (
not(isblank ( PreviousRecords ) ),
MachineHours[EngineRunningHours] - MAXX ( FILTER(MachineHours, MachineHours[id] =PreviousRecords && MachineHours[MachineName] = EARLIEST ( MachineHours[MachineName] )),[EngineRunningHours]), blank()
)

[Anonymous]

Every instance a blank record is encounterd an increment is registerd it seems. Engine_hrs_2 corresponds to formula that was provided.
 

[Anonymous]

@amitchandak thank you for your prompt reply.  After using your formula it seems that the more engine_hrs are being accounted for. There are some inconsistencies in data where it is returning the last sum of engine hrs rather then the increment per instance. I am trying to find a weekly increment for the data. The left visual corresponds to my visual while the right table corresponds to the function you provided

[amitchandak]

@Anonymous , for Week on Week comparison you can have a measure approach using date table. 

I have discussed in these blogs. see of those can help 

Power BI — Week on Week and WTD
https://medium.com/@amitchandak.1978/power-bi-wtd-questions-time-intelligence-4-5-98c30fab69d3
https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-Last-Week/ba-p/1051123
https://www.youtube.com/watch?v=pnAesWxYgJ8