Forum Discussion

paolomint's avatar
paolomint
Icon for Helper III rankHelper III
5 years ago
Solved

variation on rolling sum

Hi every one,

I have the following table:

 

Due dateCostumercredit limitdebit
01.06.2021SRD inc10.0003000
13.06.2021SRD inc10.0002000
20.06.2021SRD inc10.0001000
03.07.2021SRD inc10.000500

 

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 dateCostumercredit limitdebitresidual credit line
01.06.2021SRD inc10.000300010.000-2000-1000-500
13.06.2021SRD inc10.000200010.000-1000-500
20.06.2021SRD inc10.000100010.000-500
03.07.2021SRD inc10.000500

 

 

Following the final result I need on visualization:

 

Due dateCostumercredit limitdebitresidual credit line
01.06.2021SRD inc10.00030006500
13.06.2021SRD inc10.00020008500
20.06.2021SRD inc10.00010009500
03.07.2021SRD inc10.00050010000

 

thank you very much

Paolo

Need Help
  • v-janeyg-msft's avatar
    v-janeyg-msft
    5 years ago

    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.

3 Replies

    • paolomint's avatar
      paolomint
      Icon for Helper III rankHelper III
      5 years ago

      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

  • v-janeyg-msft's avatar
    v-janeyg-msft
    Icon for Community Support rankCommunity Support
    5 years ago

    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.