Forum Discussion
gingerclaire
Helper III
Helper IIICalculating 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...
CNENFRNL
Community Champion
Community Champion
The worksheet formula is powerful enough,
gingerclaireHelper III
Helper IIIThis 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.