Calculating difference between two values in the same column - mileage

[gingerclaire]

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 previous reading

The data looks like this:

Start time	Name	Current mileage:
3/27/20 15:09:35	Person 1	21056
3/31/20 11:13:16	Person 2	17513
5/5/20 14:49:32	Person 2	17616
5/6/20 10:03:44	Person 3	21402
5/7/20 10:14:50	Person 1	21648
6/1/20 8:54:29	Person 3	21531
6/1/20 9:11:23	Person 1	22200
6/8/20 10:36:50	Person 2	17760
7/1/20 13:37:04	Person 1	22900
7/6/20 10:02:11	Person 2	18080
7/31/20 10:08:35	Person 1	24043
8/3/20 11:52:18	Person 2	18781
9/1/20 8:47:56	Person 3	22854
9/2/20 15:54:48	Person 2	19440
9/3/20 9:29:27	Person 1	26333
9/30/20 13:28:51	Person 3	23613
10/1/20 10:17:13	Person 2	20848
10/2/20 13:48:17	Person 1	27107
10/30/20 12:12:39	Person 1	29305
11/2/20 10:09:31	Person 3	24688
11/3/20 10:52:01	Person 2	22713
11/30/20 9:49:12	Person 1	30276
12/8/20 12:55:57	Person 2	23777
12/28/20 15:18:14	Person 1	30049
1/4/21 10:28:35	Person 2	24036
1/13/21 12:46:53	Person 3	25269
1/29/21 10:06:16	Person 3	25466
2/1/21 8:26:55	Person 1	31426
2/1/21 16:11:59	Person 2	24194
3/1/21 8:56:05	Person 1	320011
3/1/21 13:40:59	Person 2	24422
3/8/21 8:43:38	Person 3	25936
3/31/21 7:46:11	Person 1	33038
3/31/21 9:59:18	Person 3	26327

I need to be able to work out monthly mileage, based on their monthly mileage readings. Because they all do this on different dates it creates problems using date in the dax

(ie I tried this difference =

sum('Car report data'[Current mileage:])-CALCULATE(SUM('Car report data'[Current mileage:]),PARALLELPERIOD('Date table'[Date],-1,MONTH))

) so i wondered if i could just calculate the difference between the most recent and previous reading for each person?

[CNENFRNL]

The worksheet formula is powerful enough,

[gingerclaire]

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]

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.

[gingerclaire]

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

[Greg_Deckler]

@gingerclaire See my article on Mean Time Between Failure (MTBF) which uses EARLIER: http://community.powerbi.com/t5/Community-Blog/Mean-Time-Between-Failure-MTBF-and-Power-BI/ba-p/3395....
The basic pattern is:
Column = 
  VAR __Current = [Value]
  VAR __PreviousDate = MAXX(FILTER('Table','Table'[Date] < EARLIER('Table'[Date])),[Date])

  VAR __Previous = MAXX(FILTER('Table',[Date]=__PreviousDate),[Value])
RETURN
  __Current - __Previous

[gingerclaire]

I must admit I came across it when searching the boards for a solution/inspiration, but as a beginner, I couldn't keep up.