Forum Discussion

kumsha1's avatar
kumsha1
Icon for Post Patron rankPost Patron
6 years ago
Solved

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-1902357...
Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    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

Nathaniel_C's avatar
Nathaniel_C
Icon for Community Champion rankCommunity Champion
6 years ago

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

Thanks,

Nathaniel

kumsha1's avatar
kumsha1
Icon for Post Patron rankPost Patron
6 years ago

Hi,

 

Last column is the expected result, i.e. DATEDIFF between Prev Delay End & Current Delay Start. Thank You.

 

UnitDelayCategoryDelay CategoryDelay TypeDelay DescriptionDelay OIDCurrent Delay StartDelay EndPrev Delay End(Hrs) Duration between previous downtime event
EX006Scheduled Down Time (SD)SDBuckets & BodiesBucket shut31511795429/09/2019 3:37:59 PM30/09/2019 10:16:49 AM  
EX006Breakdown EventsUDEngineR/H Engine low coolant shutdown31708492606/10/2019 9:50:29 PM06/10/2019 10:08:26 PM30/09/2019 10:16:49 AM155.5611111
EX006Breakdown EventsUDEngineR/H Engine shut down31739531708/10/2019 1:13:13 AM08/10/2019 1:32:43 AM06/10/2019 10:08:26 PM27.07972222
EX006Breakdown EventsUDGET BreakdownUpper wingshroud boss U/S31756355608/10/2019 3:56:20 PM08/10/2019 6:49:23 PM08/10/2019 1:32:43 AM14.39361111
EX006Breakdown EventsUDMain StructureStick retainer plate fall off.31802043810/10/2019 5:52:30 PM10/10/2019 6:57:51 PM08/10/2019 6:49:23 PM47.05194444
  • kumsha1's avatar
    kumsha1
    Icon for Post Patron rankPost Patron
    6 years ago

     Below picture of the data.

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      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's avatar
        kumsha1
        Icon for Post Patron rankPost Patron
        6 years ago

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