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  , 

 

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

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

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

        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