Forum Discussion

giovissimo's avatar
giovissimo
Helper I
5 years ago

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,...
Need Help
v-yalanwu-msft's avatar
v-yalanwu-msft
Community Support
5 years ago

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's avatar
    giovissimo
    Helper I
    5 years ago

    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's avatar
      v-yalanwu-msft
      Community Support
      5 years ago

      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's avatar
        giovissimo
        Helper I
        5 years ago

        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).