Forum Discussion
Solved
Cumulative Cashflow
Hello I want to calculate the cumulative cash flow until the third year like in the following table (per ID, JOB and YEAR), can you help me?
| ID | JOB | YEAR | CASHFLOW | CUMULATIVE CASHFLOW |
| 1 | 0 | 1 | -$ 284.563.008 | -$ 284.563.008 |
| 1 | 0 | 2 | $ 19.280.900 | -$ 265.282.108 |
| 1 | 0 | 3 | $ 11.166.200 | -$ 254.115.908 |
| 10 | 1 | 1 | -$ 284.556.992 | -$ 284.556.992 |
| 10 | 1 | 2 | $ 19.233.700 | -$ 265.323.292 |
| 10 | 1 | 3 | $ 11.168.400 | -$ 254.154.892 |
| 20 | 2 | 1 | -$ 231.575.008 | -$ 231.575.008 |
| 20 | 2 | 2 | $ 20.945.800 | -$ 210.629.208 |
| 20 | 2 | 3 | $ 10.291.400 | -$ 200.337.808 |
| 30 | 3 | 1 | -$ 266.324.992 | -$ 266.324.992 |
| 30 | 3 | 2 | $ 19.878.800 | -$ 246.446.192 |
| 30 | 3 | 3 | $ 11.137.200 | -$ 235.308.992 |
Hi OscarSuarez10 ,
To create a calculated column as below.
CUMULATIVE CASHFLOW n = CALCULATE(SUM('Table'[CASHFLOW]),FILTER('Table','Table'[ID]=EARLIER('Table'[ID]) && 'Table'[YEAR]<=EARLIER('Table'[YEAR])))
2 Replies
Hi OscarSuarez10 ,
To create a calculated column as below.
CUMULATIVE CASHFLOW n = CALCULATE(SUM('Table'[CASHFLOW]),FILTER('Table','Table'[ID]=EARLIER('Table'[ID]) && 'Table'[YEAR]<=EARLIER('Table'[YEAR])))This query is one solution to the problem.
Regards,
Mike
let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content], Transform = Table.TransformColumns(Source,{{"CASHFLOW", each Text.Remove(_,{".","$"," ", Character.FromNumber(160)}), type text}, {"CUMULATIVE CASHFLOW", each Text.Remove(_,{".","$"," ", Character.FromNumber(160)}), type text}}), #"Changed Type1" = Table.TransformColumnTypes(Transform,{{"CASHFLOW", type number}, {"CUMULATIVE CASHFLOW", type number}}), #"Grouped Rows" = Table.Group(#"Changed Type1", {"ID"}, {{"Flows", each _, type table [ID=number, JOB=number, YEAR=number, CASHFLOW=number, CUMULATIVE CASHFLOW=number]}}), #"Merged Queries" = Table.NestedJoin(#"Changed Type1", {"ID"}, #"Grouped Rows", {"ID"}, "Grouped Rows", JoinKind.Inner), #"Expanded Grouped Rows" = Table.ExpandTableColumn(#"Merged Queries", "Grouped Rows", {"Flows"}, {"Flows"}), AddCashFlow = Table.AddColumn(#"Expanded Grouped Rows", "CashFlow", each List.Sum(Table.SelectRows([Flows], (flow) => [YEAR] >= flow[YEAR])[CASHFLOW]), type number), RemoveFlows = Table.RemoveColumns(AddCashFlow,{"Flows"}) in RemoveFlows
