Forum Discussion

imjeed's avatar
imjeed
Frequent Visitor
2 years ago
Solved

Help to find difference time with current and next row

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

 

CategoryDateTimeAmount
A1-1-202300:0010$
A1-1-202300:1010$
A1-1-202300:2010$
B1-2-202320:0020$
B1-2-202320:1040$
B1-2-202320:2060$
B1-2-202320:4070$
C1-3-202317:0030$
C1-3-202317:0530$
C1-3-202317:3030$
C2-3-202317:0030$

 

The desired output will be:

 

CategoryAmount
A30$
B120$
C90$

 

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

Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    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.

2 Replies

  • rajendraongole1's avatar
    rajendraongole1
    Icon for Super User rankSuper User
    2 years ago

    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!!

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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.