Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 |
Solved! Go to 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])))
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
102 | |
53 | |
21 | |
13 | |
11 |