How to Get Difference Between Prev value and Current Value

[rahul632soni]

Hey Folks !!

I have a Table whihc consist following Column:-

VIN (Serial Number of Vehicle)

ENG_HOURS - Engine Hours ( Cumulative Number as per Timestamp)

GPS_Timestamp ( Time and Date when Data Recorded)

I want to Calculate Total Engine Hours For Each VIN over the Selected Pewriod of Time , 

I am Trying to Create a New Column with the Difference of Current Value and Prev valaue for Engine Hours and Then Taking SUM of That Calculated Column to get Exact Engine Hours , I did That in Pyspark , Can we do somethoing in Power BI Directly.

This is The Pyspark COde which i use:-

# Sort the dataframe by VIN and timestamp

nn2 = nn2.orderBy('vin', 'gps_timestamp')

# Calculate the difference in engine hours between consecutive timestamps for each VIN

nn2 = nn2.withColumn('prev_engine_hours', lag('ENG_HOURS').over(

Window.partitionBy('vin').orderBy('gps_timestamp')

))

# Calculate the exact engine hours for each VIN at each timestamp

nn2 = nn2.withColumn('exact_engine_hours', when(

col('prev_engine_hours').isNull(), 0.0

).otherwise(

col('ENG_HOURS') - col('prev_engine_hours')

))

Regards

Rahul 

[amitchandak]

@rahul632soni , earlier in DAX can help to take diff

new column=

var _max = maxx(filter(Table, [VIN] = earlier([Vin]) && [GPS_Timestamp] < earlier([GPS_Timestamp]) ), [GPS_Timestamp])

return

[ENG_HOURS] - maxx(filter(Table, [VIN] = earlier([Vin]) && [GPS_Timestamp] =_max ), [ENG_HOURS]) 

Power BI DAX- Earlier, I should have known Earlier: https://youtu.be/CVW6YwvHHi8