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

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.

IDStartEndCategoryDescriptionTypeRequired
3241058554/11/2019 6:00 SDSuper Structure shutdown  
3242548564/11/2019 21:547/11/2019 11:38UD  DATEDIFF(NULL;4/11/2019 21:54)
3245314466/11/2019 7:448/11/2019 4:00UD  DATEDIFF(7/11/2019 11:38;6/11/2019 7:44)
3245320935/11/2019 19:1312/11/2019 14:20SD  DATEDIFF(8/11/2019 4:00;5/11/2019 19:13)
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