Forum Discussion

GINMED's avatar
GINMED
Icon for Helper III rankHelper III
4 years ago
Solved

Cummulative interest payments

Hi, there

I need your help to get correct cumulative function in loan amortization schedule.

I have tried many of cummulative function, but all of them was incorect.

 

 

 

 

There is my sample data.

Sample data 

 

 

Thank you in advance !

GinMed

Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi GINMED ,

     

    I suggest you to try this code.

     

    Interest_cum =
SUMX (
    FILTER (
        ALL ( Project ),
        Project[Date Projected]
            IN VALUES ( 'Calendar'[Date] )
                && Project[Sequence] <= MIN ( Project[Sequence] )
    ),
    CALCULATE ( 'Calculation'[Interest] )
)

     

    Result is as below.

     

    Best Regards,
    Rico Zhou

     

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

4 Replies

  • PC2790's avatar
    PC2790
    Icon for Community Champion rankCommunity Champion
    4 years ago

    Hey GINMED ,

     

    I beleive you will have to make some changes to your model as I can see it is not in the star schema as Calendar is not connected to both of your tables.

    Hence, the cummulative value is not coming as expected.

    Connect your Calendar table with the Ligumi table based on the start and end date base don which you should run your calculation of cummulative values

    • GINMED's avatar
      GINMED
      Icon for Helper III rankHelper III
      4 years ago

      PC2790 , 

      that doesn't make any sence. Nothing changed:(

      Cumulative values should be taken from project table, where is interest calculation.

      • Anonymous's avatar
        Anonymous
        Not applicable
        4 years ago

        Hi GINMED ,

         

        I suggest you to try this code.

         

        Interest_cum =
SUMX (
    FILTER (
        ALL ( Project ),
        Project[Date Projected]
            IN VALUES ( 'Calendar'[Date] )
                && Project[Sequence] <= MIN ( Project[Sequence] )
    ),
    CALCULATE ( 'Calculation'[Interest] )
)

         

        Result is as below.

         

        Best Regards,
        Rico Zhou

         

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