Sum previous row by multiple checks

[Ronald123]

Hello,

I have the follow challange. I need a calculated colum with the follow calculation rules.

If the [DATE] && [CARD] is the same like the previous row and the time range of the colum [TIME] is
max.  5 minutes later than de previous time && [TYPE]=REFUND then i need on the max date row the sum of the colum [ AMOUNT].

I hope someone can help me.

Ronald 

[Phil_Seamark · Accepted solution]

Hi @Ronald123

Here is a crack at a calculated column.  I have attached a PBIX file with my working.

Column = 
VAR LastValue = 
    SUMX(
        FILTER(
            'Table1',
            'Table1'[DATE] = EARLIER('Table1'[DATE]) &&
            'Table1'[CARD] = EARLIER('Table1'[CARD]) && 
            'Table1'[TIME] <= EARLIER('Table1'[TIME]) && 

            'Table1'[TIME] >= EARLIER('Table1'[TIME]) - "00:05:00"
            ),
       'Table1'[Amount])
VAR NextValue =        
           SUMX(
        FILTER(
            'Table1',
            'Table1'[DATE] = EARLIER('Table1'[DATE]) &&
            'Table1'[CARD] = EARLIER('Table1'[CARD]) && 
            'Table1'[TIME] > EARLIER('Table1'[TIME]) && 
             'Table1'[Type] = EARLIER('Table1'[Type]) &&
            'Table1'[Type] = "Refund" &&
            'Table1'[TIME] <= EARLIER('Table1'[TIME]) + "00:05:00"
            ),
       'Table1'[Amount])
RETURN 
    IF(
        NextValue>0,
        blank(),
        LastValue)
    
    

[Ronald123]

@Phil_Seamark, Thanks, it works fine.