Calculate km's from a fuel purchase summary with odometer readings


#1

Sam, I am wondering how to create a measure that calculates distance from 2 or more odometer and date records. As an example record 1, “1/1/18 - 20000 km” record 2, “1/2/18 - 25000 km”. I need a measure that calculates the difference in km’s to give me the distance over the period (5000 km). Thanks in advance. Damien


#2

The first thing to do is definitely break out a column like this into multiple columns in the query editor.

You should have one column as the date and then another with the km amount.

Having data setup like this is almost useless really in Power BI, but that is what the query editor is perfect for.

So you just need to split the columns basically in the correct place.

Then you calculation is a piece of cake.

Just one minus the other I would say.

See how you go with these ideas.

Chrs


#3

Thanks Sam.

I did not explain myself very well.

This is the way the records are currently in the table.

Headers Date Odometer Rego
1/1/2018 15000 XYZ123
1/2/2018 20000 XYZ123

I am struggling to create a measure that can be used to calculate distance travelled over the time period filtered by Vehicle rego.

Thanks again


#4

Ok understand more now.

The first thing I think you should do here is create an index column.

This way if the same registration pops up then you have a column that can refer to the closest input.

You can do this here

image

But you might not need this if you just have the Rego mentioned once.

If that’s the case then you might actually get away with something like this

Trip Distance = 
VAR StartOdometer = FIRSTNONBLANK( 'Travel Distance'[Odometer], [Total Trips] )
VAR EndOdometer = LASTNONBLANK( 'Travel Distance'[Odometer], [Total Trips] )

RETURN
EndOdometer - StartOdometer

Total trips I’m calculating like this

Total Trips = COUNTROWS( 'Travel Distance' ) / 2

image

I’ve attached the mock up I created.

See what you think

Odometer Readings.pbix (44.0 KB)