Forum Discussion

rsbin's avatar
rsbin
Community Champion
4 years ago
Solved

Engine Telematics for Distance Travelled - EARLIER function?

Good Day Folks,

Need some assistance here, I think using the Earlier function.  I have a dataset of Engine Telematics and Diagnostics.

My first step is to calculate the Distance Travelled the previous day for each Vehicle in the fleet, given the Odometer Reading at the beginning of each Day.  An example for one vehicle as follows:

DateUTCDeviceNameOdometerDistance Travelled
31-Mar-22HC-06-03428 
30-Mar-22HC-06-0340919
29-Mar-22HC-06-0339118
28-Mar-22HC-06-033892

End Result will be going into a Matrix Visual.  So am open to using a Measure or Calculated Column, but preference I think would be a Measure.

Any and all assistance appreciated.

Thanks and Best Regards,

Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi rsbin ,

     

    We will use EARLIER() to catch current value in calculated column. In measure we will use SUM()/MAX() to catch current value.

    You can try this code to create a measure.

     

    Distance Travelled = 
VAR _NextDay =
    CALCULATE (
        SUM ( 'Table'[Odometer] ),
        FILTER (
            ALLEXCEPT ( 'Table', 'Table'[DeviceName] ),
            'Table'[DateUTC]
                = MAX ( 'Table'[DateUTC] ) + 1
        )
    )
VAR _CurrentDay =
    CALCULATE ( SUM ( 'Table'[Odometer] ) )
VAR _DIFF = _NextDay - _CurrentDay
RETURN
    IF ( _DIFF < 0, BLANK (), _DIFF )

     

    Result is as below.

     

    Best Regards,
    Rico Zhou

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

5 Replies

    • rsbin's avatar
      rsbin
      Community Champion
      4 years ago

      Good Morning AlexisOlson ,

      Thanks much for the reply.  I do understand that EARLIER refers to a method to obtain a row context.

      I have just never used it before.

      I believe the model I am creating will be an SSAS Tabular, so Power Query won't be available to me.  I did see there were Calc Column solutions in the link you sent me.  I will play with those and see if I can make it work in my case.

      Thanks again, much appreciated!

      • Anonymous's avatar
        Anonymous
        Not applicable
        4 years ago

        Hi rsbin ,

         

        We will use EARLIER() to catch current value in calculated column. In measure we will use SUM()/MAX() to catch current value.

        You can try this code to create a measure.

         

        Distance Travelled = 
VAR _NextDay =
    CALCULATE (
        SUM ( 'Table'[Odometer] ),
        FILTER (
            ALLEXCEPT ( 'Table', 'Table'[DeviceName] ),
            'Table'[DateUTC]
                = MAX ( 'Table'[DateUTC] ) + 1
        )
    )
VAR _CurrentDay =
    CALCULATE ( SUM ( 'Table'[Odometer] ) )
VAR _DIFF = _NextDay - _CurrentDay
RETURN
    IF ( _DIFF < 0, BLANK (), _DIFF )

         

        Result is as below.

         

        Best Regards,
        Rico Zhou

         

        If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.