cancel
Showing results for
Did you mean:
New Member

## Difference between two rows

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?

1 ACCEPTED SOLUTION
Microsoft

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

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Microsoft

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

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Announcements

#### Power BI May 2023 Update

Find out more about the May 2023 update.

#### Data Stories Gallery

Share your Data Story with the Community in the Data Stories Gallery.

Top Solution Authors
Top Kudoed Authors