Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
willfarkas
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 IDRefuelling DateQuantity litersRegistered Mileage
Amay-01101000
Bmay-011599999
Cmay-01108888
Aapr-2920900
Bapr-281099988
Capr-27158800
Aapr-2010850
Bapr-202099777
Capr-20108700

 

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
v-yulgu-msft
Employee
Employee

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.

View solution in original post

1 REPLY 1
v-yulgu-msft
Employee
Employee

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.