variation on rolling sum

[paolomint]

Hi every one,

I have the following table:

Due date	Costumer	credit limit	debit
01.06.2021	SRD inc	10.000	3000
13.06.2021	SRD inc	10.000	2000
20.06.2021	SRD inc	10.000	1000
03.07.2021	SRD inc	10.000	500

Considering that each debt is paid exactly on the due date, I need to calculate the residual bank credit taking into account the future debts already recorded. Following in the last colum the calculation I need to obtain:

Due date	Costumer	credit limit	debit	residual credit line
01.06.2021	SRD inc	10.000	3000	10.000-2000-1000-500
13.06.2021	SRD inc	10.000	2000	10.000-1000-500
20.06.2021	SRD inc	10.000	1000	10.000-500
03.07.2021	SRD inc	10.000	500

Following the final result I need on visualization:

Due date	Costumer	credit limit	debit	residual credit line
01.06.2021	SRD inc	10.000	3000	6500
13.06.2021	SRD inc	10.000	2000	8500
20.06.2021	SRD inc	10.000	1000	9500
03.07.2021	SRD inc	10.000	500	10000

thank you very much

Paolo

[v-janeyg-msft · Accepted solution]

Hi, @paolomint 

You can try like this:

Column =
'Table'[credit limit]
    - SUMX (
        FILTER (
            'Table',
            [Due date] > EARLIER ( 'Table'[Due date] )
                && [Costumer] = EARLIER ( 'Table'[Costumer] )
        ),
        [debit]
    )

Best Regards

Janey Guo

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

[amitchandak]

@paolomint , a new column like

[credit limit] - sumx(filter(Table, [Due date] > earlier([Due date])),[debit])

[paolomint]

Thank you very much  @amitchandak 

I'm really sorry, I made the example to easy.

Infact in the same table I have several companies and I need to calculate the residual credit row by row, client by client.

Please apologise.

I hope you can help me

Ciao

Paolo