How to calculate a new column to plot the number of hours elapsed Vs Fuel consumed

[Art]

I have table with the above two columns. I want to calculate the time elapsed in terms of number of hours between two time stamps and also the diffrence in fuel consumed during those times and store them as two new columns and then plot a line chart of hours Vs Fuel consumed

[v-piga-msft]

Hi @Art，

You could refer to the two calculated column below.

Before creating the calculated colum, you need to create the index column in Query Editor.

Then you could create the calculated column below.

difference for time =
VAR prevous =
    CALCULATE (
        MIN ( 'Table1'[FuelUseddatetime] ),
        FILTER ( 'Table1', 'Table1'[Index] > EARLIER ( 'Table1'[Index] ) )
    )
RETURN
    IF (
        ISBLANK ( prevous ),
        'Table1'[FuelUseddatetime],
        DATEDIFF ( [FuelUseddatetime], [Previous], HOUR )
    )

difference for furlused =
VAR NextIndex = Table1[Index] + 1
RETURN
    - (
        Table1[FuelUsed]
            - CALCULATE (
                VALUES ( Table1[FuelUsed] ),
                FILTER ( ALL ( Table1 ), Table1[Index] = NextIndex )
            )
    )

Then you could get the differences.

If you need additional help, please share your desired output, so that we can help further investigate on it?

Best Regards,

Cherry

[Art]

Can the last row with negative value be eliminated?

[Art]

HI! cherry,

Thanks for the reply. The difference in time DAX is not working. You have used the spelling prevous in two places. in the DATEDIFF function you have used Previous. Shouldn't all three have the same spelling? I tried all three with the same spelling but I get an error saying the prevous in the DATEDIFF function cannot be found or may not be used in this expression. Can you please copy paste this Block of coded from your power BI expression window. I wonder why its giving error even if I make them all the same spelling.

[Art]

should the [Previous] in the DATEDIFF function be just prevous?