DATEDIFF between previous row date and current row date

[kumsha1]

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 !

[Nathaniel_C]

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.
Nathaniel

[kumsha1]

Thanks 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]

Hi @kumsha1 
Would you please give us an example of expected output?

Thanks,

Nathaniel

[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.

[v-shex-msft · Accepted solution]

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 / 3600

Regards,

Xiaoxin Sheng

[kumsha1]

Thank You Sheng, this works perfet with minor changes as required.