Forum Discussion
Problem Calculating the Kilometers driven
Hello,
first of all, sorry if my problem has been solved already; I found a similar question, but not exactly the same.
My table contains the following fields: Date (which is not relevant), Driver, Car, TotalKms, DrivenKms.
We own two cars in my family, and each of us can drive either car everyday. In each row I want to write down the total kilometers for each car, and have a column calculate the kilometers driven (DrivenKms) after each drive, but taking into account the car.
How can I solve it ?
Thank you in advance.
Giovanni
22 Replies
giovissimo , Try a new column like
DrivenKms =
[TotalKms]- maxx(filter(Table, [Car] =earlier([car]) && [TotalKms] < earlier([TotalKms])),[TotalKms])Thank you, but it doesn't work apparently: I get and answer like (the following is my translation from Italian) "it's not possible to determine a single value for the Kms column. It may depend on a formula which refers to a column containing multiple values without an aggregation function like Sum to obtain a single result".
I tried to modify your formula with Sumx(TotalKms - etcetera) but it summarizes the values by driver.
I also realized that the date is important, because if the table were not ordered by ascending date, the calculated column wouldn't make any sense.
The goal is to obtain the driven kilometers by subtracting the previous total to the newest (for the same car).
Giovanni
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.
Hi,
Share some data and show the expected result.
Hi,
What end result do you want to see?
Hi, giovissimo ,
In your data, In order to distinguish the same kilometers from the same date, you may have to use the index column as an auxiliary column. and then create a column as follows:
DrivenKms = VAR _last = CALCULATE ( MAX ( [TotalKms] ), FILTER ( ALLEXCEPT ( 'Table1', 'Table1'[Car] ), [Index] < EARLIER ( [Index] ) ) ) VAR _a = 'Table1'[Index] RETURN IF ( CALCULATE ( SUM ( [TotalKms] ), FILTER ( 'Table1', 'Table1'[Index] = _a - 1 || 'Table1'[Index] = _a ) ) / 2 = [TotalKms], 0, [TotalKms] - _last )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.Thanks for replying in the first place.
Unfortunately, the error message is the same as the one prieviously mentioned (i.e., that the row context EARLIER refers to actually doesn't exist).
Translated, it says that EARLIER refers to a row context which doesn't exist
Hi , giovissimo ,
so you have to add index column, steps as follows:
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.
