Duration Calculation

[Puja]

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 name	DateTime	Event	Result
Product 1	7/19/2022 6:38:25 PM	8
Product 1	7/19/2022 6:47:20 PM	55
Product 1	7/19/2022 7:29:31 PM	55
Product 1	7/19/2022 8:17:36 PM	55
Product 1	7/19/2022 8:30:29 PM	9999	23 min 24 sec
Product 1	7/19/2022 8:53:53 PM	55
Product 1	7/19/2022 9:00:21 PM	55
Product 1	7/19/2022 9:16:29 PM	55
Product 1	7/19/2022 9:25:21 PM	55
Product 1	7/19/2022 9:32:16 PM	55
Product 1	7/19/2022 9:47:20 PM	7777
Product 1	7/19/2022 10:32:48 PM	55
Product 1	7/19/2022 10:45:27 PM	55
Product 1	7/19/2022 10:53:31 PM	55
Product 1	7/19/2022 11:15:18 PM	55
Product 1	7/19/2022 11:33:29 PM	55
Product 1	7/19/2022 11:45:45 PM	55

[Jihwan_Kim · Accepted solution]

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 · Accepted solution]

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

[Puja]

@Jihwan_Kim , Thanks for your help .
I see this error . Any thoughts ?

[Jihwan_Kim · Accepted solution]

Hi,

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

[Puja]

Thank you @Jihwan_Kim . Its working as expected :).