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

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