Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
CheenuSing
Community Champion
Community Champion

Help in building cashflow table with interest computed on diminishing balance

 

Hi @Zubair_Muhammad ,  @OwenAuger , @MattAllington 

 

My table has only the columns MonthName, Month, Cashflow

 

                      Month    Cashflow      Interest    liability    liability c/f

 
Aug-18 0      6,838.17                        6,838.17
Sep-18 1 -300 28.49 271.51                  6,566.66
Oct-18 2 -300 27.36 272.64                    6,294.02
Nov-18 3 -300 26.23 273.77                  6,020.25
Dec-18 4 -300 25.08 274.92                    5,745.33
Jan-19 5 -300 23.94 276.06                  5,469.27
Feb-19 6 -300 22.79 277.21                    5,192.06
Mar-19 7 -300 21.63 278.37                  4,913.69
Apr-19 8 -300 20.47 279.53                    4,634.17
May-19 9 -300 19.31 280.69                  4,353.48
Jun-19 10 -300 18.14 281.86                    4,071.62
Jul-19 11 -300 16.97 283.03                  3,788.58
Aug-19 12 -300 15.79 284.21                    3,504.37
Sep-19 13 -300 14.60 285.40                  3,218.97
Oct-19 14 -300 13.41 286.59                    2,932.38
Nov-19 15 -300 12.22 287.78                  2,644.60
Dec-19 16 -300 11.02 288.98                    2,355.62
Jan-20 17 -300 9.82 290.18                  2,065.43
Feb-20 18 -300 8.61 291.39                    1,774.04
Mar-20 19 -300 7.39 292.61                  1,481.43
Apr-20 20 -300 6.17 293.83                    1,187.60
May-20 21 -300 4.95 295.05                      892.55
Jun-20 22 -300 3.72 296.28                        596.27
Jul-20 23 -300 2.48 297.52                      298.76
Aug-20 24 -300 1.24 298.76                          0.00

 

Need help to compute interest, Liablilty,Liability C/F

 

The formula for Interest = ( Previous Month Liability C/F) * (0.05/12) 

The formula for  Liability  =  -Cashflow-Interest

Liability C/F = PreviousMonthLiability C/F - Liability

 

Note that for the Month = 0, the interest, Liability is Zero.

 

How this can be achieved in PowerQuery or DAX when want to show the final output as a table visual.

 

Appreciate any help on this

 

Cheers

 

CheenuSing

Did I answer your question? Mark my post as a solution and also give KUDOS !

Proud to be a Datanaut!
2 ACCEPTED SOLUTIONS
OwenAuger
Super User
Super User

Hi @CheenuSing 

 

These sort of iterative calculations can be done with either Power Query or DAX. I have attached an example showing both.

 

  1. Using Power Query, you can use List.Generate to create a list containing the Interest and other values, in a similar fashion to a While-loop. The M code I used is below, with the List.Generate portion in red. The list is then converted to a table and joined with the original table.
    Possibly this could be converted into a more general function:
    let
        Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ZdC9CsIwFIbhW5HMjeQk/UnHgjgI6uBYOtRSXESLWMG7tz1fweQ0kOVJIDlvXatqvGnyKlFm2pt55Yl3fkuFapJaXfoBxzRt7YxhPXdvqA319PxAXai7voOmoR7ah6ZykizUfX+F5qEe2xe0CLUaFvXx3S+0jF4bl9fIxHxfOJqOkzBH43EKZrdqwZyuYjBnqxrMucxh579RIXuAvQwCLmURZmtkEjDJJmArm4CdbAL+T9n8AA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [MonthName = _t, Month = _t, Cashflow = _t]),
        ChangeType1 = Table.TransformColumnTypes(Source,{{"MonthName", type date}, {"Month", Int64.Type}, {"Cashflow", type number}}),
        SortedRows = Table.Sort(ChangeType1,{{"Month", Order.Ascending}}),
        Cashflow = SortedRows[Cashflow],
        CashflowCount = List.Count(Cashflow),
        CalcInterest =
            List.Generate(
                ()=>[Index = 0,Interest = null, Liability = null, LiabilityCF = Cashflow{0}],
                each [Index]<CashflowCount,
                each [
                    Index = [Index]+1,
                    Interest = [LiabilityCF]*0.05/12,
                    Liability = -Cashflow{Index}-Interest,
                    LiabilityCF = [LiabilityCF]-Liability
                ]
            ),
        ConvertedToTable = Table.FromList(CalcInterest, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
        ExpandedColumns = Table.ExpandRecordColumn(ConvertedToTable, "Column1", {"Index", "Interest", "Liability", "LiabilityCF"}, {"Index", "Interest", "Liability", "Liability c/f"}),
        JoinTables = Table.NestedJoin(SortedRows,{"Month"},ExpandedColumns,{"Index"},"Interest"),
        #"Expanded Interest" = Table.ExpandTableColumn(JoinTables, "Interest", {"Interest", "Liability", "Liability c/f"}, {"Interest", "Liability", "Liability c/f"}),
        ChangeType2 = Table.TransformColumnTypes(#"Expanded Interest",{{"Interest", type number}, {"Liability", type number}, {"Liability c/f", type number}})
    in
        ChangeType2
  2. Using DAX, you can create calculation columns as follows:
    Liability c/f is calculated by compounding each cashflow value (positive or negative) at the monthly interest rate, by the number of months between the cashflow and the current row's month. The other columns can then be derived from this:
    Liability c/f DAX = 
    VAR InterestRatePerMonth =
        0.05/12
    VAR CurrentMonth =
        Data[Month]
    VAR CashflowToDate =
        CALCULATETABLE ( 
            ADDCOLUMNS (
                SUMMARIZE( Data, Data[Month] ),
                "CashflowSum", CALCULATE ( SUM ( Data[Cashflow] ) )
            ),
            Data[Month] <= CurrentMonth,
            ALL ( Data )
        )
    VAR LiabilityCF =
        SUMX ( 
            CashflowToDate,
            [CashflowSum] * ( 1 + InterestRatePerMonth ) ^ ( CurrentMonth - Data[Month] )
        )
    RETURN
        LiabilityCF
    
    Interest DAX = 
    VAR InterestRatePerMonth =
        0.05/12
    VAR CurrentMonth =
        Data[Month]
    VAR Interest =
        CALCULATE (
            SUM ( Data[Liability c/f DAX] ),
            ALL ( Data ),
            Data[Month] = CurrentMonth - 1
        ) * InterestRatePerMonth
    RETURN
        Interest
    
    Liability DAX = 
    IF ( 
        Data[Month] > 0,
        - Data[Cashflow] - Data[Interest DAX]
    )

Hopefully that's a useful illustration of how these sorts of calculations can be done. 

 

Cheers 
Owen 🙂


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn

View solution in original post

Zubair_Muhammad
Community Champion
Community Champion

@CheenuSing 

 

Similar to List.Generate used by @OwenAuger  we can also use List.Accumulate

 

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ZdBJCgMxEEPRqwSv26HkuXOVpu9/jdhksFAtBIK3+9cVDNFGxHjYyywcYa2NPJ7o4T4+fpJjLuZ5vwgjTIogzIqJsAguP39YFRNhU8yEXbEQDsVKeCo2QphqZ9VGK/BWjbTybnWVjFUzgTNBO4E7wYVCTPZXVyqxulSZ1bUqpMm1qqyuVWN1rTqrtpqdSXer+w0=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Month Name" = _t, Month = _t, Cashflow = _t]),
ChangedType = Table.TransformColumnTypes(Source,{{"Month Name", type datetime}, {"Month", Int64.Type}, {"Cashflow", type number}}),
PL=Table.AddColumn(ChangedType,"Liability CF",
each
let mymonth=[Month],
myfirstCashflow=Table.SelectRows(ChangedType,each [Month]=0)[Cashflow]{0},
mylist=Table.SelectRows(ChangedType, each [Month]<=mymonth and [Month]>0)[Cashflow] in
List.Accumulate(mylist, myfirstCashflow, (state, current) => state + state*.05/12 + current))
in
PL

CS.png

 


Regards
Zubair

Please try my custom visuals

View solution in original post

11 REPLIES 11
Zubair_Muhammad
Community Champion
Community Champion

@CheenuSing 

 

Similar to List.Generate used by @OwenAuger  we can also use List.Accumulate

 

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ZdBJCgMxEEPRqwSv26HkuXOVpu9/jdhksFAtBIK3+9cVDNFGxHjYyywcYa2NPJ7o4T4+fpJjLuZ5vwgjTIogzIqJsAguP39YFRNhU8yEXbEQDsVKeCo2QphqZ9VGK/BWjbTybnWVjFUzgTNBO4E7wYVCTPZXVyqxulSZ1bUqpMm1qqyuVWN1rTqrtpqdSXer+w0=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Month Name" = _t, Month = _t, Cashflow = _t]),
ChangedType = Table.TransformColumnTypes(Source,{{"Month Name", type datetime}, {"Month", Int64.Type}, {"Cashflow", type number}}),
PL=Table.AddColumn(ChangedType,"Liability CF",
each
let mymonth=[Month],
myfirstCashflow=Table.SelectRows(ChangedType,each [Month]=0)[Cashflow]{0},
mylist=Table.SelectRows(ChangedType, each [Month]<=mymonth and [Month]>0)[Cashflow] in
List.Accumulate(mylist, myfirstCashflow, (state, current) => state + state*.05/12 + current))
in
PL

CS.png

 


Regards
Zubair

Please try my custom visuals

Hi @Zubair_Muhammad , @OwenAuger 

 

Thanks to you both for giving a solution. 

 

It will be for the benefit of  every one in the community , if you can explain what each step does in PowerQuery and in DAX.

 

 

Thanks again

 

Cheers

 

CheenuSing

Did I answer your question? Mark my post as a solution and also give KUDOS !

Proud to be a Datanaut!

Hi @CheenuSing 

 

Here is my effort to explain List.Accumulate

 

List.Accumualte is one of the ways of implementing a loop in Power Query (others being List.Generate, recursive function).

 

This loop is similar to For -Next loop in VBA

 

Formula:
List.Accumulate(list as list, seed as any, accumulator as function)as any 


Example given in https://docs.microsoft.com/en-us/powerquery-m/list-accumulate
List.Accumulate({1, 2, 3, 4, 5}, 0, (state, current) => state + current) equals 15 

Sample illustration with your data (Row 3 calculation)

cs1.png

 

 


Regards
Zubair

Please try my custom visuals
OwenAuger
Super User
Super User

Hi @CheenuSing 

 

These sort of iterative calculations can be done with either Power Query or DAX. I have attached an example showing both.

 

  1. Using Power Query, you can use List.Generate to create a list containing the Interest and other values, in a similar fashion to a While-loop. The M code I used is below, with the List.Generate portion in red. The list is then converted to a table and joined with the original table.
    Possibly this could be converted into a more general function:
    let
        Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ZdC9CsIwFIbhW5HMjeQk/UnHgjgI6uBYOtRSXESLWMG7tz1fweQ0kOVJIDlvXatqvGnyKlFm2pt55Yl3fkuFapJaXfoBxzRt7YxhPXdvqA319PxAXai7voOmoR7ah6ZykizUfX+F5qEe2xe0CLUaFvXx3S+0jF4bl9fIxHxfOJqOkzBH43EKZrdqwZyuYjBnqxrMucxh579RIXuAvQwCLmURZmtkEjDJJmArm4CdbAL+T9n8AA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [MonthName = _t, Month = _t, Cashflow = _t]),
        ChangeType1 = Table.TransformColumnTypes(Source,{{"MonthName", type date}, {"Month", Int64.Type}, {"Cashflow", type number}}),
        SortedRows = Table.Sort(ChangeType1,{{"Month", Order.Ascending}}),
        Cashflow = SortedRows[Cashflow],
        CashflowCount = List.Count(Cashflow),
        CalcInterest =
            List.Generate(
                ()=>[Index = 0,Interest = null, Liability = null, LiabilityCF = Cashflow{0}],
                each [Index]<CashflowCount,
                each [
                    Index = [Index]+1,
                    Interest = [LiabilityCF]*0.05/12,
                    Liability = -Cashflow{Index}-Interest,
                    LiabilityCF = [LiabilityCF]-Liability
                ]
            ),
        ConvertedToTable = Table.FromList(CalcInterest, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
        ExpandedColumns = Table.ExpandRecordColumn(ConvertedToTable, "Column1", {"Index", "Interest", "Liability", "LiabilityCF"}, {"Index", "Interest", "Liability", "Liability c/f"}),
        JoinTables = Table.NestedJoin(SortedRows,{"Month"},ExpandedColumns,{"Index"},"Interest"),
        #"Expanded Interest" = Table.ExpandTableColumn(JoinTables, "Interest", {"Interest", "Liability", "Liability c/f"}, {"Interest", "Liability", "Liability c/f"}),
        ChangeType2 = Table.TransformColumnTypes(#"Expanded Interest",{{"Interest", type number}, {"Liability", type number}, {"Liability c/f", type number}})
    in
        ChangeType2
  2. Using DAX, you can create calculation columns as follows:
    Liability c/f is calculated by compounding each cashflow value (positive or negative) at the monthly interest rate, by the number of months between the cashflow and the current row's month. The other columns can then be derived from this:
    Liability c/f DAX = 
    VAR InterestRatePerMonth =
        0.05/12
    VAR CurrentMonth =
        Data[Month]
    VAR CashflowToDate =
        CALCULATETABLE ( 
            ADDCOLUMNS (
                SUMMARIZE( Data, Data[Month] ),
                "CashflowSum", CALCULATE ( SUM ( Data[Cashflow] ) )
            ),
            Data[Month] <= CurrentMonth,
            ALL ( Data )
        )
    VAR LiabilityCF =
        SUMX ( 
            CashflowToDate,
            [CashflowSum] * ( 1 + InterestRatePerMonth ) ^ ( CurrentMonth - Data[Month] )
        )
    RETURN
        LiabilityCF
    
    Interest DAX = 
    VAR InterestRatePerMonth =
        0.05/12
    VAR CurrentMonth =
        Data[Month]
    VAR Interest =
        CALCULATE (
            SUM ( Data[Liability c/f DAX] ),
            ALL ( Data ),
            Data[Month] = CurrentMonth - 1
        ) * InterestRatePerMonth
    RETURN
        Interest
    
    Liability DAX = 
    IF ( 
        Data[Month] > 0,
        - Data[Cashflow] - Data[Interest DAX]
    )

Hopefully that's a useful illustration of how these sorts of calculations can be done. 

 

Cheers 
Owen 🙂


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn

@OwenAuger 

how can the dax code be adjusted if the interest rate is not constant and changes occassionally? please help. thank you

Hi @OwenAuger 

I am basically trying to achieve a table similar to below using DAX columns.

tandy87_0-1672817341120.png

I have already seen a measure-based implementation of this (link below) although I can't seem to get it to work in a column format. (https://www.sqlbi.com/articles/computing-the-future-value-of-an-investment-based-on-compound-growth-...

Please help. Thank you.

 



Hi @tandy87 

I realise this is a late reply on your most recent question in this thread.

 

The SQLBI article contains some very interesting and comprehensive measures 🙂

 

For your scenario, you could create a column like this, following similar but simplified logic:

FV = 
VAR OuterYear =
    Cashflow[Year]
RETURN
    CALCULATE (
        SUMX (
            Cashflow,
            VAR CurrentCF =
                Cashflow[CF]
            VAR InnerYear =
                Cashflow[Year]
            VAR GrowthFactor =
                CALCULATE (
                    PRODUCTX (
                        Cashflow,
                        (1 + Cashflow[Rate] )
                    ),
                    Cashflow[Year] > InnerYear,
                    Cashflow[Year] <= OuterYear,
                    ALL ( Cashflow )
                )
            VAR GrowthFactorFinal =
                COALESCE ( GrowthFactor, 1 )
            RETURN
                CurrentCF * GrowthFactorFinal
        ),
        Cashflow[Year] <= OuterYear,
        ALL ( Cashflow )
    )

See attached PBIX.

 

Regards,


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn

Hi  @OwenAuger ,

 

I have the table with   ID ,MonthName, Month,Cashflow . 

Thatis for different IDs I have similar data as posted earlier.

 

What changes should be made to dax when user filter a particular ID through slicer.

 

And also to Power Query.

 

Sorry I forgot to mmention this in my earler post.

 

Cheers

 

CheenuSing

Did I answer your question? Mark my post as a solution and also give KUDOS !

Proud to be a Datanaut!

Hi @OwenAuger ,

 

I got it working. Thanks for the help.

 

If you can put down in simple sentences what the calculated columns  and power query does in each step it will help everyone understand the logic better.

 

 

Cheers

 

CheenuSing

Did I answer your question? Mark my post as a solution and also give KUDOS !

Proud to be a Datanaut!

Hi again @CheenuSing 

Took me a while to come back to this.

I have added some comments to the code describing the meaning of the important steps.

Pasted below and reattached my PBIX.

 

It sounded like you were fine with handling the calculation for different "project IDs" or something similar? Post back if neeeded 🙂

 

Liability c/f DAX = 
VAR InterestRatePerMonth =
    0.05/12
VAR CurrentMonth =
    Data[Month]
// CashflowToDate is a table of Cashflow by Month, for all months up to and including the current row's month
VAR CashflowToDate =
    CALCULATETABLE ( 
        ADDCOLUMNS (
            SUMMARIZE( Data, Data[Month] ),
            "CashflowSum", CALCULATE ( SUM ( Data[Cashflow] ) )
        ),
        // Filter to all months <= current row's month
        Data[Month] <= CurrentMonth, 
        // Clear other filters present in the current row.
        // Note: If you want to do this calculation "per Project" or similar, 
        // ALL could be changed to ALLEXCEPT to keep filters on specific columns, such as Project ID
        ALL ( Data ) 
                     
    )
// LiabilityCF is the result we want
VAR LiabilityCF =
    // Iterate over the rows of CashflowToDate
    SUMX ( 
        CashflowToDate,
        // For each row, take the cashflow and compound it by the interest rate,
        // from the month of the cashflow up to the month of the current row in the Data table
        [CashflowSum] * ( 1 + InterestRatePerMonth ) ^ ( CurrentMonth - Data[Month] )
    )
RETURN
    LiabilityCF

Interest DAX = 
VAR InterestRatePerMonth =
    0.05/12
VAR CurrentMonth =
    Data[Month]
// Interest is equal to the balance from the previous month multiplied by the interest rate
VAR Interest =
    // Get balance from current row's month minus 1
    CALCULATE (
        SUM ( Data[Liability c/f DAX] ),
        ALL ( Data ),
        Data[Month] = CurrentMonth - 1
    ) * InterestRatePerMonth
RETURN
    Interest

Liability DAX = 
// This is effectively the movement in Liability c/f (the monthly balance)
// which is (-Cashflow)+(-Interest).
// This could also be simply calculated as the change in Liability c/f
IF ( 
    // Only calculate for Month > 0
    Data[Month] > 0,
    - Data[Cashflow] - Data[Interest DAX]
)

Also here's the M code again with comments:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ZdC9CsIwFIbhW5HMjeQk/UnHgjgI6uBYOtRSXESLWMG7tz1fweQ0kOVJIDlvXatqvGnyKlFm2pt55Yl3fkuFapJaXfoBxzRt7YxhPXdvqA319PxAXai7voOmoR7ah6ZykizUfX+F5qEe2xe0CLUaFvXx3S+0jF4bl9fIxHxfOJqOkzBH43EKZrdqwZyuYjBnqxrMucxh579RIXuAvQwCLmURZmtkEjDJJmArm4CdbAL+T9n8AA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [MonthName = _t, Month = _t, Cashflow = _t]),
    ChangeType1 = Table.TransformColumnTypes(Source,{{"MonthName", type date}, {"Month", Int64.Type}, {"Cashflow", type number}}),
    SortedRows = Table.Sort(ChangeType1,{{"Month", Order.Ascending}}),
    Cashflow = SortedRows[Cashflow],
    CashflowCount = List.Count(Cashflow),
    // List.Generate is used to create a list containing the required values for each time index
    // Each element of the list is a record containing fields Index, Interest, Liability, LiabilityCF
    // Each record is calculated by taking the previous record's values plus the cashflow of the current index
    CalcInterest =
        List.Generate(
            // Initialise variables
            // Index (month ) = 0, Interest = null (i.e. zero), LiabilityCF = first cashflow
            ()=>[Index = 0,Interest = null, Liability = null, LiabilityCF = Cashflow{0}],
            
            // This is the loop condition: continue as long as Index < the count of cashflows
            each [Index]<CashflowCount,
            
            // Each time through the loop, add the following record to the list of results.
            // This record contains:
            // Index = Previous Index + 1
            // Interest = Previous Liability c/f * interest rate
            // Liability = - Cashflow{Index} - Interest (above)
            // Liability c/f = Previous Liability c/f  less Liability (above)
            each [
                Index = [Index]+1,
                Interest = [LiabilityCF]*0.05/12,
                Liability = -Cashflow{Index}-Interest,
                LiabilityCF = [LiabilityCF]-Liability
            ]
        ),
    // Convert CalcInterst to a table & expand the records into columns
    ConvertedToTable = Table.FromList(CalcInterest, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    ExpandedColumns = Table.ExpandRecordColumn(ConvertedToTable, "Column1", {"Index", "Interest", "Liability", "LiabilityCF"}, {"Index", "Interest", "Liability", "Liability c/f"}),
    // Join CalcInterest to the original table
    JoinTables = Table.NestedJoin(SortedRows,{"Month"},ExpandedColumns,{"Index"},"Interest"),
    #"Expanded Interest" = Table.ExpandTableColumn(JoinTables, "Interest", {"Interest", "Liability", "Liability c/f"}, {"Interest", "Liability", "Liability c/f"}),
    ChangeType2 = Table.TransformColumnTypes(#"Expanded Interest",{{"Interest", type number}, {"Liability", type number}, {"Liability c/f", type number}})
in
    ChangeType2

Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn

Thanks Owen

 

Cheers

 

CheenuSing

Did I answer your question? Mark my post as a solution and also give KUDOS !

Proud to be a Datanaut!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.