Forum Discussion
DATEDIFF between previous row date and current row date
Hi,
Looking for help on below dataset to calculate DATEDIFF.
For OID: 190235781 the date difference required would be Delay End of previous row (OID-190233439) - current Delay Start (OID-190235781). Thanks in advance !
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
7 Replies
Hi 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.
NathanielThanks 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)
