Forum Discussion
Problem Calculating the Kilometers driven
Hi giovissimo ,
You could create a measure by the following formula:
DrivenKms =
VAR _next =
CALCULATE (
MAX ( [TotalKms] ),
FILTER ( ALLEXCEPT ( 'Table', 'Table'[Car] ), [Date ] < EARLIER ( [Date ] ) ))
RETURN
IF ( [TotalKms] <> [TotalKms] - _next, [TotalKms] - _next )
The final output is shown below:
Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thank you for replying, nonetheless the error shown is the following (translated from Italian):
"EARLIER/EARLIEST refers to a previous row context which doesn't exist".
I'm at a loss to understand the reason of such a message.
Giovanni
Hi, giovissimo
EARLER() is used for column, not measure. If you use measure, you can use Max ().as follows:
DrivenKms2 = VAR _next = CALCULATE ( MAX ( [TotalKms] ), FILTER ( ALLEXCEPT ( 'Table', 'Table'[Car] ), [Date ] < MAX ( [Date ] ) ) ) RETURN IF ( MAX ( [TotalKms] ) <> MAX ( [TotalKms] ) - _next, MAX ( [TotalKms] ) - _next )I hope it can help you. can you share some error images or share me with your PBIX file after removing sensitive data.
Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.Thanks for replying.
This works just fine, except in 2 cases:
1) for each car, the initial value of the calculated column cannot be blank, but it must be set equal to the total km;
2) when the driver simply refuels, without actually increasing the number of total kms, the calculated column must return 0.
Here's the link to the data:
https://1drv.ms/x/s!AiJ_Ge6bLlC6-U-ti0wqDRuebJ6q?e=4x4off
Giovanni