Difference between two rows

[willfarkas]

Hi folks,

I am trying to create a matrix that shows the fuel consumption from different equipments of a fleet, given the difference between the last reffuling and its previous refuelling.

The formula would be like: [(today refuelling mileage) - (previous refuelling mileage)] / (quantity of fuel filled today)

The issue is how to bring the penultimate refuelling entry, as min and max cannot work as I can have 3 or 4 entries but it only interests the last and the penultimate entries.

The database has the following key columns:

Equipment ID	Refuelling Date	Quantity liters	Registered Mileage
A	may-01	10	1000
B	may-01	15	99999
C	may-01	10	8888
A	apr-29	20	900
B	apr-28	10	99988
C	apr-27	15	8800
A	apr-20	10	850
B	apr-20	20	99777
C	apr-20	10	8700

So, the fuel consumption for equipment "A" between refuelling on may 01 and april 29 was:

Fuel consumption (mileage/liter): (1000) - (900) / (10) = 10 mi/liter

Any suggestions on how can I get this on PowerBI, please?

[v-yulgu-msft · Accepted solution]

Hi @willfarkas,

In this scenario, please first create a Rank column:

Rank =
RANKX (
    FILTER (
        'Difference between two rows',
        'Difference between two rows'[Equipment ID]
            = EARLIER ( 'Difference between two rows'[Equipment ID] )
    ),
    'Difference between two rows'[Refuelling Date],
    ,
    ASC,
    DENSE
)

Then, use this rank column to calculate 'Fuel consumption':

Fuel consumption =
 (
    'Difference between two rows'[Registered Mileage]
        - LOOKUPVALUE (
            'Difference between two rows'[Registered Mileage],
            'Difference between two rows'[Equipment ID], 'Difference between two rows'[Equipment ID],
            'Difference between two rows'[Rank], 'Difference between two rows'[Rank] - 1
        )
)
    / 'Difference between two rows'[Quantity liters]

Best regards,
Yuliana Gu