Forum Discussion
Post PatronDATEDIFF between previous row date and current row date
Hi kumsha1 ,
You can try to below calculate column formula to get last end DateTime and calculate the duration between two DateTime fields:
hour = VAR diff = DATEDIFF ( CALCULATE ( MAX ( 'Table'[Delay End] ), FILTER ( 'Table', [Unit] = EARLIER ( 'Table'[Unit] ) && [Current Delay Start] < EARLIER ( 'Table'[Current Delay Start] ) ) ), [Current Delay Start], SECOND ) RETURN diff / 3600Regards,
Xiaoxin Sheng
Community ChampionHi kumsha1 ,
Had a hard time seeing your picture, so made a quick table.
Time diff =
var _endPresent = MAX('myTable'[end])
var _begPresent = MAX(myTable[beg])
var _endPrevious = CALCULATE(MAX('myTable'[end]),'myTable'[end]<_endPresent,ALLEXCEPT(myTable,myTable[end]))
return DATEDIFF(_endPrevious ,_begPresent,DAY)
This should work for you when you insert your table and column names. Use variables and calculate to solve.
Let me know if you have any questions.
If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos 👍are nice too.
Nathaniel
Post PatronThanks for your response.
Being new to PBI, i am not sure i am doing wrong as the DATEDIFF is giving me wrong values. Below is the actual data for your reference.
| ID | Start | End | Category | Description | Type | Required |
| 324105855 | 4/11/2019 6:00 | SD | Super Structure shutdown | |||
| 324254856 | 4/11/2019 21:54 | 7/11/2019 11:38 | UD | DATEDIFF(NULL;4/11/2019 21:54) | ||
| 324531446 | 6/11/2019 7:44 | 8/11/2019 4:00 | UD | DATEDIFF(7/11/2019 11:38;6/11/2019 7:44) | ||
| 324532093 | 5/11/2019 19:13 | 12/11/2019 14:20 | SD | DATEDIFF(8/11/2019 4:00;5/11/2019 19:13) |
- Nathaniel_C
- kumsha1
Hi,
Last column is the expected result, i.e. DATEDIFF between Prev Delay End & Current Delay Start. Thank You.
Unit DelayCategory Delay Category Delay Type Delay Description Delay OID Current Delay Start Delay End Prev Delay End (Hrs) Duration between previous downtime event EX006 Scheduled Down Time (SD) SD Buckets & Bodies Bucket shut 315117954 29/09/2019 3:37:59 PM 30/09/2019 10:16:49 AM EX006 Breakdown Events UD Engine R/H Engine low coolant shutdown 317084926 06/10/2019 9:50:29 PM 06/10/2019 10:08:26 PM 30/09/2019 10:16:49 AM 155.5611111 EX006 Breakdown Events UD Engine R/H Engine shut down 317395317 08/10/2019 1:13:13 AM 08/10/2019 1:32:43 AM 06/10/2019 10:08:26 PM 27.07972222 EX006 Breakdown Events UD GET Breakdown Upper wingshroud boss U/S 317563556 08/10/2019 3:56:20 PM 08/10/2019 6:49:23 PM 08/10/2019 1:32:43 AM 14.39361111 EX006 Breakdown Events UD Main Structure Stick retainer plate fall off. 318020438 10/10/2019 5:52:30 PM 10/10/2019 6:57:51 PM 08/10/2019 6:49:23 PM 47.05194444 - kumsha1
Below picture of the data.