How to get row to row difference by Group/sort

[harshagraj]

Hello all, I need to group/sort by ModulNr and StationsNr and need the datediff in secs by row2 - row1,row3-row2. Attached the data and also screenshot. Please note I am in a direct query mode. 

Thanks upfront.

ModulNr	StationsNr	ModuleStn	PreStopperArrival_C	Expected Result in Secs
8	1	M8-S1	24-11-2020 14:26:21
8	1	M8-S1	24-11-2020 14:26:30	9
8	1	M8-S1	24-11-2020 14:26:36	5
8	1	M8-S1	24-11-2020 14:29:10
7	1	M7-S1	24-11-2020 14:29:26
6	1	M6-S1	24-11-2020 14:29:51
5	1	M5-S1	24-11-2020 14:30:09
7	1	M7-S1	24-11-2020 14:30:27
6	1	M6-S1	24-11-2020 14:30:45
5	1	M5-S1	24-11-2020 14:31:02

@amitchandak @Anonymous @ryan_mayu @Anonymous @parry2k 

[ryan_mayu]

@harshagraj 

maybe you can try to create a measure

Measure = 
VAR arrive=max('Table'[PreStopperArrival_C])
VAR Previous=MAXX(FILTER(all('Table'),'Table'[ModulNr]=max('Table'[ModulNr])&&'Table'[StationsNr]=max('Table'[StationsNr])&&'Table'[PreStopperArrival_C]<arrive),'Table'[PreStopperArrival_C])
return if(ISBLANK(Previous),BLANK(),SECOND(arrive-Previous))

[harshagraj]

Hello @ryan_mayu  thanks a lot. Its working perfectly. But I need to plot Barchart. X axis will have Concatenated Module Station Nr and Y axis i have to consider Avergae Carriertime.

Carriertime(Measure) = Above calculated field  minus(-) Prestoparrdiff

Prestoparrdiff(Measure)=DATEDIFF(SELECTEDVALUE(CTM_Timestamps[PreStopperArrival_C]),SELECTEDVALUE(CTM_Timestamps[DepositCarrier_C]),SECOND)

will the above calculation works with Barchart? I tried but ended up in wrong values

[ryan_mayu]

@harshagraj 

I keep working on the previous sample data. you can try to create a measure

average = averagex(CTM_Timestamps,[Carriertime])

please see the attachment below

[harshagraj]

Hello @ryan_mayu thanks a lot for the help. I just exactly did the same steps. But I am getting below error. My data size is huge actually. Every 5 seconds i get 8 records for different modules and from diff stations

[Icey]

Hi @harshagraj ,

Replace calculated column 'CTM_Timestamps'[Run] with a measure and try it again.

Best regards

Icey

If this post helps, then consider Accepting it as the solution to help other members find it faster.

[ryan_mayu]

@harshagraj 

I seldom us direct query. can pbi display the bottom left table on your end? That table contains the same fields and measures as the chart.

[Anonymous]

1) Why directquery 

2) What is you source

[Anonymous]

Read this 

https://exceltown.com/en/tutorials/power-bi/powerbi-com-and-power-bi-desktop/power-bi-data-sources/p...

[harshagraj]

Thanks @Anonymous  but I am on Direct query mode.