Forum Discussion
Calculating difference between two values in the same column - mileage
I have a spreadsheet from a car report that my team fill in monthly on different dates. Their mileage they enter is their odometer reading, so i need to work out the difference between that and their...
The worksheet formula is powerful enough,
This was a geat help - but now it has thrown up another issue:
I have a mileage spreadsheet and in it I am using the following measure to work out how much mileage they have done in each month:
Mileage since last month = 'Car report data'[Current mileage:]-'Car report data'[Prev Reading]
To get Prev Reading I am using the following calculated column:
Prev Reading =
MAXX(
TOPN(
1,
FILTER(
'Car report data',
'Car report data'[Car reg] = EARLIER( 'Car report data'[Car reg] )
&& 'Car report data'[Start time] < EARLIER( 'Car report data'[Start time] )
),
'Car report data'[Start time]
),
'Car report data'[Current mileage:]
)
The problem is that when there is no previous reading, then this throws out a negative number.
Would I better saying if there is no previous reading it should = 0? How would i do this?
The issue is that some of the drivers have had several different cars, but I want to see their miles driven each month by driver (regardless of car) so that I can then forecast their mileage needs (to ensure their contract allows for this).
Any help gratefully received.
Hi gingerclaire ,
According to your description, your formula is 'Car report data'[Current mileage:]-'Car report data'[Prev Reading], if there is no previous reading, how can it get the negative number, can't reproduce it.
But if that's the case, here's my solution:
Mileage since last month2 = 'Car report data'[Current mileage:] - IF ( 'Car report data'[Prev Reading] <> BLANK (), 'Car report data'[Prev Reading], 0 )Best Regards,
Community Support Team _ kalyjIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
