How to sum diffently positioned rows in various columns?

[baravo]

Hi,  I need to calculate a column with the closing balance of each day. The closing balance becomes the opening balance for the next day.

Opening balance + Variables_SUM = Closing balance

In Excel, there are 2 steps to calculate this column:

1. First day (I3): Sum the opening balance with the Variables_SUM from that day - this gets the Closing balance of the first day (in my example, it's 595.324)

2. Second day on (I4 and rest): Sum the closing balance of the first day with the Variables_SUM from the second day (see image).

Step 1

Step 2

I post this table as an example in case you want to replicate it in Power BI.

I really appreciate your help. Thank you!

	Variables				How to get this in Power BI?
Date	VAR1 - minus	VAR2 - minus	VAR3 - plus	VAR4 - minus	Opening balance 1st day	Closing balance 1st day	Variables_SUM	Cash flow calculation
21/04/2021	282.335	-	446.302	55.808	487.165	595.324	108.159	595.324
22/04/2021	912.919	-	397.557	55.808	-	-	-                     571.170	24.154
23/04/2021	187.327	200.000	400.891	55.808	-	-	-                       42.244	-                                18.090
24/04/2021	-	-	21.918	-	-	-	21.918	3.828
25/04/2021	-	-	28.059	-	-	-	28.059	31.887
26/04/2021	111.859	-	599.218	55.808	-	-	431.552	463.438
27/04/2021	160.553	-	608.805	55.808	-	-	392.444	855.882
28/04/2021	442.175	-	446.302	55.808	-	-	-                       51.681	804.201
29/04/2021	723.623	-	397.557	55.808	-	-	-                     381.874	422.327
30/04/2021	259.049	-	400.891	55.808	-	-	86.034	508.361
01/05/2021	-	-	21.918	-	-	-	21.918	530.279
02/05/2021	-	-	28.059	-	-	-	28.059	558.338
03/05/2021	84.049	-	599.218	55.808	-	-	459.362	1.017.699
04/05/2021	11.620	-	608.805	55.808	-	-	541.377	1.559.076
05/05/2021	178.000	-	446.302	55.808	-	-	212.494	1.771.569
06/05/2021	-	750.000	397.557	55.808	-	-	-                     408.251	1.363.318

[Anonymous · Accepted solution]

Hi @baravo ,

Based on your description, you can create a calculated column as follows.

Cash flow calculation =
MAX ( 'Test'[Opening balance 1st day] )
    + SUMX (
        FILTER ( ALL ( 'Test' ), [Date] <= EARLIER ( Test[Date] ) ),
        [Variables_SUM]
    )

Result:

Hope that's what you were looking for.

Best Regards,

Yuna

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@baravo , You have to create a measure like

Sumx(all(Table), [Opening balance]) + CALCULATE(Sumx(Table,[Var3] -[Var2] -[Var1] -[Var4]) ,filter(allselected(Table),Table[Date] <=max(Table[Date])))