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
Solved! Go to Solution.
Hi @CheenuSing
These sort of iterative calculations can be done with either Power Query or DAX. I have attached an example showing both.
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
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
Proud to be a Datanaut!
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
Hi @CheenuSing
These sort of iterative calculations can be done with either Power Query or DAX. I have attached an example showing both.
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
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
Proud to be a Datanaut!
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
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
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
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)
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
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
Proud to be a Datanaut!
Thanks Owen
Cheers
CheenuSing
User | Count |
---|---|
83 | |
69 | |
60 | |
45 | |
45 |