cancel
Showing results for
Search instead for
Did you mean:
Highlighted
Member

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

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team

## Re: Cumulative Cashflow

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

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.
2 REPLIES 2
Established Member

## Re: Cumulative Cashflow

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```
Community Support Team

## Re: Cumulative Cashflow

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

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.

## Helpful resources

Announcements

#### Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

#### Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

#### January 2020 Community Highlights

Make sure you didn't miss any of the things that happened in the community in January!

Top Solution Authors
Top Kudoed Authors