Forum Discussion

rahul632soni's avatar
rahul632soni
Icon for Helper I rankHelper I
3 years ago

How to Get Difference Between Prev value and Current Value

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 

Need Help

1 Reply

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    3 years ago

    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