Problem Calculating the Kilometers driven

[giovissimo]

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

[v-yalanwu-msft]

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.

[giovissimo]

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

[v-yalanwu-msft]

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.

[giovissimo]

I've bypassed the problem by importing the table in Access, inserting the Index (counter) column and then exporting it again in Excel, from where I imported it in PowerBI 🙂

Nonetheless, the error message remains 😞 

[giovissimo]

Thank you, nonetheless apparently I have a different menu, and when I try to add a new column, I don't get to specifiy the type of data, since as I click on to the "Tipo di Dati" menu on the left, the column disappears.

My version is the 2.92.1067.0 64-bit (April 2021). 

[Ashish_Mathur]

Hi,

Share some data and show the expected result.

[giovissimo]

Here's the data:

https://1drv.ms/x/s!AiJ_Ge6bLlC6-U-ti0wqDRuebJ6q?e=4x4off

Thank you

[Ashish_Mathur]

Hi,

What end result do you want to see?

[giovissimo]

The one you see in the "DrivenKms" column:

https://1drv.ms/x/s!AiJ_Ge6bLlC6-U-ti0wqDRuebJ6q?e=tDwltm

Giovanni

[Ashish_Mathur]

Hi,

You may download my PBI file from here.

Hope this helps.

[giovissimo]

Thank you, only I don't have any index column in my table: is it absolutely necessary to add it ?

Is it linked to any existing column ?

Giovanni

[Ashish_Mathur]

You are welcome.  The Index column was created in the Query Editor.  See the steps in the Query Editor. 

[giovissimo]

Thank you again for devoting your time to the solution of my problem.

I've just taken a look at all the steps in the query editor, but unfortunately I can't understand the meaning of that Index column (it's obviously my fault). As far as I can see, it's not linked to the driver, neither to the car.

Isn't it possible to solve the problem without resorting to that Index column ? Just asking.

Giovanni

[Ashish_Mathur]

You are welcome.  The only reason we need the Index column is to get the 0's that you have shown in the intiial post.

[giovissimo]

I see, but it's not an automatic counter, right ? So, when I have to add another row, what value am I supposed to write for the Index column ?

Giovanni

[Ashish_Mathur]

It is automatic.  You just need to add data to your raw data sheet and click on Home > Refresh All.

[v-yalanwu-msft]

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.  

[giovissimo]

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

[v-yalanwu-msft]

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.

[giovissimo]

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