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
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.
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.
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
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
I’ve attached the mock up I created.
See what you think
Odometer Readings.pbix (44.0 KB)
Thanks for your example and help. Due to work committments I have only had a chance recently to get back to this issue.
I cannot get your measures to work correctly with my data model.
It is showing me the total distance travelled at the bottom of the table but not individual trip distance.
I have attached my test report and data.
Is it possible to be more specific around the exact issue, laying out the problem formula etc.
As much assistance as possible to target in on the exact problem is what I’m looking for.
Based on the information you’ve given most recently and from looking at the model quickly, I’m unsure as to what I should be looking at the also what formula I’m meant to help with.
I hope @sam.mckay doesn’t mind, but I took a stab at this. I did a lot of the heavy lifting in Power Query, so please be sure to take a look at those in the attached. My goal was to get current row’s transaction odometer reading to “move down” one row as that would be that rows starting odometer reading. But first had to be sure to group by Reg Number (don’t want overlapping car info) and then want to be sure the dates are sorted in an ascending manner. You can do with creating an Index column starting 1 and another one starting from 0 and merging the table with itself ( left outer join using those index columns created).
That will give us the previous row’s odometer reading, but to get the trip end data you would do the exact same thing except flip the order of columns you are merging. This is much clearer in the file I promise. But here’s the final table in PQ:
Now that are table is setup in a good way, we can write some measures. I like to explicitly like to write every measure ( even as something as simple as sum ):
Total Current Odometer = SUM ( Table1[Transaction Odometer Reading] ) Trip End Date New = LASTDATE( Table1[Prev Date] ) Total Trips New = COUNTROWS( Table1 )
Then I made it an option how you wanted to handle the 1st transaction date that you filter on. Can start at zero, or pick up the previous value ( even if not visible in your table):
Prev Odometer Zero on First Date Selected = If ( MAX( Table1[Transaction Effective Date]) = CALCULATE( FIRSTDATE( Table1[Transaction Effective Date]), ALLSELECTED(Table1[Transaction Effective Date] ) ),0, [Total Prev Odometer] )
Then a switch statement to show which one ( probably wont even use this, but I was working on something similar so I thought why not)
Previous Odometer Selected = SWITCH( [Odometer Selected], "Ignore Previous Odometer, Start at Zero", [Prev Odometer Zero on First Date Selected], "Use Previous Odometer", [Total Prev Odometer], [Total Prev Odometer] )
Then the final matrix:
I’m sure there are probably somethings that wont work 100%, but this should hopefully be of some help!
Here’s the file:
Westvic Vehicle Reporting Testing v2.pbix (99.1 KB)