Help to find difference time with current and next row

[imjeed]

Hi all,

Need a help to find the difference time between the current and next row that have the same ID, below are scenarios:
 
I have a category of transactions with date, time, and amount.
 
I am looking to sum the amounts for each category of transactions that occur within 30MIN

Category	Date	Time	Amount
A	1-1-2023	00:00	10$
A	1-1-2023	00:10	10$
A	1-1-2023	00:20	10$
B	1-2-2023	20:00	20$
B	1-2-2023	20:10	40$
B	1-2-2023	20:20	60$
B	1-2-2023	20:40	70$
C	1-3-2023	17:00	30$
C	1-3-2023	17:05	30$
C	1-3-2023	17:30	30$
C	2-3-2023	17:00	30$

The desired output will be:

Category	Amount
A	30$
B	120$
C	90$

Can anyone help me how to find that?
 
Thank you in advance.

[Anonymous · Accepted solution]

Hi @imjeed 

Thanks for the reply from @rajendraongole1 .

@imjeed , the following measure is for your reference.

Measure = 
VAR _min = CALCULATE(MIN([Time]), ALLEXCEPT('Table', 'Table'[Category]))
VAR _date = MIN('Table'[Date])
RETURN
CALCULATE(SUM('Table'[Amount]), FILTER(ALLEXCEPT('Table', 'Table'[Category]), [Date] = _date && [Time] <= _min + TIME(0, 30, 0)))

Output:

Best Regards,
Yulia Xu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[rajendraongole1]

Hi @imjeed - First combine date and time column by taking calculated column

DateTime = 'Daxxy'[Date] + 'Daxxy'[Time]

then find the timedifference calculated column as below:

TimeDifference =

VAR CurrentCategory = 'Daxxy'[Category]

VAR CurrentDateTime = 'Daxxy'[DateTime]

VAR NextDateTime =

    CALCULATE(

        MIN('Daxxy'[DateTime]),

        FILTER(

            'Daxxy',

            'Daxxy'[Category] = CurrentCategory &&

            'Daxxy'[DateTime] > CurrentDateTime

        )

    )

RETURN

IF(

    ISBLANK(NextDateTime),

    BLANK(),

    DATEDIFF(CurrentDateTime, NextDateTime, MINUTE)

)

last create an another measure with below:

TotalAmountWithin30Minutes =

VAR CurrentCategory = MAX('Daxxy'[Category])

RETURN

CALCULATE(

    SUM('Daxxy'[Amount]),

    FILTER(

        'Daxxy',

        'Daxxy'[Category] = CurrentCategory &&

        (

            VAR CurrentDateTime = 'Daxxy'[DateTime]

            RETURN

            CALCULATE(

                COUNTROWS('Daxxy'),

                FILTER(

                    'Daxxy',

                    'Daxxy'[Category] = CurrentCategory &&

                    DATEDIFF(CurrentDateTime, 'Daxxy'[DateTime], MINUTE) <= 30

                )

            ) > 1

        )

    )

)

final result as , ihave removed the $ sign as summazation is not accepting in text., converted to numeric.

Hope it works for you.

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!