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

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.