Forum Discussion

Puja's avatar
Puja
Helper III
4 years ago
Solved

Duration Calculation

Hello Community,

I need some help to calculate the duration based on an event.

Based on those highlighted (screenshot below) dates , I need to calculate the duration.

Anytime , 9999  or 7777  events occurs , duration should be calculed with next immidiate event datetime which is always 55.

TIA

 

Sample data

Product nameDateTimeEventResult
Product 17/19/2022 6:38:25 PM8 
Product 17/19/2022 6:47:20 PM55 
Product 17/19/2022 7:29:31 PM55 
Product 17/19/2022 8:17:36 PM55 
Product 17/19/2022 8:30:29 PM999923 min 24 sec
Product 17/19/2022 8:53:53 PM55 
Product 17/19/2022 9:00:21 PM55 
Product 17/19/2022 9:16:29 PM55 
Product 17/19/2022 9:25:21 PM55 
Product 17/19/2022 9:32:16 PM55 
Product 17/19/2022 9:47:20 PM7777 
Product 17/19/2022 10:32:48 PM55 
Product 17/19/2022 10:45:27 PM55 
Product 17/19/2022 10:53:31 PM55 
Product 17/19/2022 11:15:18 PM55 
Product 17/19/2022 11:33:29 PM55 
Product 17/19/2022 11:45:45 PM55 

 

 

 

  • Jihwan_Kim's avatar
    Jihwan_Kim
    4 years ago

    Hi,

    Please check the below picture and the attached pbix file.

    I tried to create a sample pbix file like below, and it is for creating a new column.

     

     

    Result seconds CC =
VAR _tableperproductnextdatetime =
    FILTER (
        ADDCOLUMNS (
            ADDCOLUMNS (
                Data,
                "@nextdatetime",
                    MINX (
                        FILTER (
                            Data,
                            Data[Product name] = EARLIER ( Data[Product name] )
                                && Data[DateTime] > EARLIER ( Data[DateTime] )
                        ),
                        Data[DateTime]
                    )
            ),
            "@diff", DATEDIFF ( Data[DateTime], [@nextdatetime], SECOND )
        ),
        Data[Event] IN { 9999, 7777 }
    )
RETURN
    SUMMARIZE (
        FILTER (
            _tableperproductnextdatetime,
            Data[Product name] = EARLIER ( Data[Product name] )
                && Data[Event] = EARLIER ( Data[Event] )
        ),
        [@diff]
    )
  • Jihwan_Kim's avatar
    Jihwan_Kim
    4 years ago

    Hi,

    Please share your sample pbix file's link, and then I can try to look into it to come up with a solution.

4 Replies

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Super User
    4 years ago

    Hi,

    Please check the below picture and the attached pbix file.

    I tried to create a sample pbix file like below, and it is for creating a new column.

     

     

    Result seconds CC =
VAR _tableperproductnextdatetime =
    FILTER (
        ADDCOLUMNS (
            ADDCOLUMNS (
                Data,
                "@nextdatetime",
                    MINX (
                        FILTER (
                            Data,
                            Data[Product name] = EARLIER ( Data[Product name] )
                                && Data[DateTime] > EARLIER ( Data[DateTime] )
                        ),
                        Data[DateTime]
                    )
            ),
            "@diff", DATEDIFF ( Data[DateTime], [@nextdatetime], SECOND )
        ),
        Data[Event] IN { 9999, 7777 }
    )
RETURN
    SUMMARIZE (
        FILTER (
            _tableperproductnextdatetime,
            Data[Product name] = EARLIER ( Data[Product name] )
                && Data[Event] = EARLIER ( Data[Event] )
        ),
        [@diff]
    )
      • Jihwan_Kim's avatar
        Jihwan_Kim
        Super User
        4 years ago

        Hi,

        Please share your sample pbix file's link, and then I can try to look into it to come up with a solution.