Engine Telematics for Distance Travelled - EARLIER function?

[rsbin]

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:

DateUTC	DeviceName	Odometer	Distance Travelled
31-Mar-22	HC-06-03	428
30-Mar-22	HC-06-03	409	19
29-Mar-22	HC-06-03	391	18
28-Mar-22	HC-06-03	389	2

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,

[AlexisOlson]

EARLIER refers to an outer row context rather than having anything to do with time.

There are several solutions to calculating differences from cumulative totals in this post:

https://community.powerbi.com/t5/Desktop/Calculating-Daily-values-from-Cumulative-Total/m-p/2198969

[rsbin]

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!

[v-rzhou-msft · Accepted solution]

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.

[rsbin]

@v-rzhou-msft,

Thank you for the response.  I will give this solution a try.  Thank you for the explanation, and am pretty sure I can follow your logic in this.  Will confirm once I am able to get back to this.

Thanks again and Best Regards,

[rsbin]

@v-rzhou-msft ,

Apologies for the long delay in my response. Business travel and other priorities took me away from this one for a while.  Now back at it.  However, the orginal dataset has now changed.  I have spent considerable time trying to resolve, but unable to.  Here is the new twist.  Instead of one value per day where I could use SUM, I now get a value every hour.  So I need to find the first value of the day and subtract it from the first value of the next day for each vehicle.  In this example I have changed Odometer to Duration (i.e Engine Run Time).  I believe I will need to apply the same solution to generate both Distance Travelled and Engine Time in my final project.

DeviceID	Date	Duration
HSCP-2149	5/3/2022 0:47	5925060
HSCP-2149	5/2/2022 22:44	5907960
HSCP-2149	5/2/2022 21:44	5904360
HSCP-2149	5/2/2022 21:04	5902020
HSCP-2149	5/2/2022 20:46	5900940
HSCP-2149	5/2/2022 20:43	5900760
HSCP-2149	5/2/2022 20:26	5899680
HSCP-2149	5/2/2022 0:43	5897160
HSCP-2150	5/3/2022 0:47	492506
HSCP-2150	5/2/2022 20:46	482800
HSCP-2150	5/2/2022 0:45	482300

Your kind assistance would be much appreciated and once again, my apologies for the delay in responding to your earlier generous response.

Kindest Regards,