Forum Discussion

gingerclaire's avatar
gingerclaire
Helper III
4 years ago

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...
  • gingerclaire's avatar
    gingerclaire
    Helper III
    4 years ago

    Thank you - is that a measure or a calculated column please?

     

  • gingerclaire's avatar
    gingerclaire
    Helper III
    4 years ago

    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.
    • v-yanjiang-msft's avatar
      v-yanjiang-msft
      Community Support
      4 years ago

      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 _ kalyj

      If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.