Retrieving the previous ID for a calculated column

[giovissimo]

Hello,

first of all, sorry for posting my question again: I just wanted to shift it here from the original forum ("desktop"), but haven't found anywhere the way to do it, so please don't kill me, at least not this time :-).

My problem is as follows: I have a table containing the following fields: Date, Driver, Car, TotalKms, plus DrivenKms which is a calculated column, and that's the reason why I am posting.

We own two cars in my family, and each of us can drive either car everyday. After using a car, in each row I I want to take note of  the day, the driver, which car has been used, the total kilometers, and then have a column calculate the kilometers driven (DrivenKms) from the previous drive. The table is ordered by ascending date. The Dax formula should therefore retrieve the previous use of the same car and calculate the difference in kilometers.

The proposed solution was:

DrivenKms =
[TotalKms]- maxx(filter(Table, [Car] =earlier([car]) && [TotalKms] < earlier([TotalKms])),[TotalKms])

but something must be missing, because it doesn't work, and unfortunately I am too rusty to do it myself.

How can I solve it ?

Thank you in advance.

Giovanni

[amitchandak]

@giovissimo , You are creating this as a new column ?can share data with issue

formula seem fie at high level

DrivenKms =
[TotalKms]- maxx(filter(Table, [Car] =earlier([car]) && [TotalKms] < earlier([TotalKms])),[TotalKms])

[giovissimo]

Here's the link to the data:

https://1drv.ms/u/s!AiJ_Ge6bLlC6-U4k9j8VCvQ-Adx9?e=i3eTjx

Giovanni

[amitchandak]

@giovissimo , if you have datetime you can use that. else you have add an index column and try that

Column = [TotalKms] -maxx(FILTER('Table (2)', [Datetime] <EARLIER('Table (2)'[Datetime])),[TotalKms])

Column = [TotalKms] -maxx(FILTER('Table (2)', [index] <EARLIER('Table (2)'[index])),[TotalKms])

index column - add in power query https://stackoverflow.com/questions/45715963/creating-an-index-column-for-power-bi

[giovissimo]

Thank you.

The thing is that Dax does't accept [TotalKms] at the beginning of the formula.

The (translated) error message  is "it's not possible to determine the value for [TotalKms]. The column either doesn't exist or there's no current row for this column".

The problem was there with the previous formula you sent me too, but that one took into consideration that the car be the same, which is a necessary condition to avoid confusion between the kilometers of the two cars.

Giovanni