end / start balance based of mesures

[ReutAtias12]

I created a matrix in which each row is based on mesure 

		Q1	Q2	Q3	Q4
sales rep name	start balance	0	1250	1750	12750
	new business	1000		5000
	up -sell	250	500	6000
	churn
	end balance	1250	1750	12750	12750

the start balance is supposed to be the end balance from the last quarter

and the end balance is supposed to be the total of all the measures in the same quarter 

Start Balance = 
IF(CALCULATE([Total Targets], PREVIOUSQUARTER(dimDates[Date])) = 0 , 
0,
 CALCULATE([End Balance], PREVIOUSQUARTER(dimDates[Date])))

End Balance = 
var _cyc = [New Business] + [UP - Sell] + [ Churn]
var _cycLQ = CALCULATE([New Business] + [UP - Sell] + [ Churn],PREVIOUSQUARTER(dimDates[Date]))
return 
IF(CALCULATE([Total Targets],PREVIOUSQUARTER(dimDates[Date])) = 0,
_cyc,
[Start Balance] +_cyc)

the end balance is not working (I can't use the [start balance] in the [end balance]) 

how can I fix that? 

[v-yueyunzh-msft]

Hi , @ReutAtias12 

According to your description, you want to calculate the sum of the measures in row headers?

If this , you can refer to this:
(1)This is my test data:

(2)I create two measures to test:

(3)Then we need to create  a dimension table to put on the Matrix visual:

(4)Then we need to create a measure like this:

Value = var _row = MAX('Row'[Column1])
return
SWITCH(_row,
"Start Balance" , [Start Balance] , 
"Test Sum" , [Test Sum],
"Total" , [Start Balance]+ [Test Sum]
)

(5)Then we can pot the fields we need on the Matix visual and we can meet your need:

If this method does not meet your needs, you can provide us with your special sample data and the desired output sample data in the form of tables, so that we can better help you solve the problem.

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

Best Regards,

Aniya Zhang

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

[amitchandak · Accepted solution]

@ReutAtias12 , to me it seems like a case of cumulative measure

beginning

calculate( [New Business] + [UP - Sell] + [ Churn], filter(all(Date), Date[Date]< Min(Date[Date])))

End

calculate( [New Business] + [UP - Sell] + [ Churn], filter(all(Date), Date[Date]<= max(Date[Date])))

[ReutAtias12]

can my start point for the cumulative calculation be only after [total targets] on the PREVIOUS QUARTER = 0

CALCULATE([Total Targets], PREVIOUSQUARTER(dimDates[Date])) = 0