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 ...
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