Cumulative Cashflow

[OscarSuarez10]

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

[v-frfei-msft · Accepted solution]

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])))

[Anonymous]

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