difference between consecutive dates rows

[alevandenes]

Hi Community,

i need some help to write a formula.

For every driver, as you can see in overview below, i have some transaction dates and the mileage that they recorded in the system.

I need to calculate the difference between the two consecutive dates. in particular, i need a formula that calculates automatically that driver X has driven 1374 Kms (168128 minus 155754) between 16-oct-2019 and 30-oct-2019.

could you help me with this?

Kind regards

[v-yangliu-msft · Accepted solution]

Hi  @alevandenes,

Here are the steps you can follow：

1. Create measure.

difference between the two consecutive dates =
var _closestdate=CALCULATE(MAX('Table'[Date]),FILTER(ALL('Table'),'Table'[driver]=MAX('Table'[driver])&&'Table'[Date]<MAX('Table'[Date])))
return
MAX('Table'[Milage]) -  CALCULATE(SUM('Table'[Milage]),FILTER(ALL('Table'),'Table'[driver]=MAX('Table'[driver])&&'Table'[Date]=_closestdate))

driverX_date =
var _date1016=CALCULATE(SUM('Table'[Milage]),FILTER(ALL('Table'),'Table'[Date]=DATE(2019,10,16)&&'Table'[driver]="driver X"))
var _date1030=CALCULATE(SUM('Table'[Milage]),FILTER(ALL('Table'),'Table'[Date]=DATE(2019,10,30)&&'Table'[driver]="driver X"))
return
_date1030 - _date1016

2. Result:

Best Regards,

Liu Yang

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

[amitchandak]

@alevandenes , a new column or measure

new column =
var _max = maxx(filter(Table, [Diver] = earlier([Driver]) && [Date] < earlier([date])),[Date])
return
[KMS] - maxx(filter(Table, [Diver] = earlier([Driver]) && [Date] =_max),[KMS])

new measure =
var _max = maxx(filter(allselected(Table), [Diver] = max([Driver]) && [Date] < max([date])),[Date])
return
sum([KMS]) - maxx(filter(allselected(Table), [Diver] = max([Driver]) && [Date] =_max),[KMS])