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

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?

 

IDJOBYEARCASHFLOWCUMULATIVE CASHFLOW
101-$ 284.563.008-$   284.563.008
102 $    19.280.900-$   265.282.108
103 $    11.166.200-$   254.115.908
1011-$ 284.556.992-$   284.556.992
1012 $    19.233.700-$   265.323.292
1013 $    11.168.400-$   254.154.892
2021-$ 231.575.008-$   231.575.008
2022 $    20.945.800-$   210.629.208
2023 $    10.291.400-$   200.337.808
3031-$ 266.324.992-$   266.324.992
3032 $    19.878.800-$   246.446.192
3033 $    11.137.200-$   235.308.992

 

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Cumulative Cashflow

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

Capture.PNG

 

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

View solution in original post

2 REPLIES 2
mcybulski Established Member
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
Community Support Team

Re: Cumulative Cashflow

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

Capture.PNG

 

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

View solution in original post

Helpful resources

Announcements
Exclusive LIVE Community Event #3 – Ask Arun Anything

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!

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

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