topic Re: end / start balance based of mesures in DAX Commands and Tips

end / start balance based of mesures

ReutAtias12 — Mon, 02 Jan 2023 14:25:42 GMT

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?

Re: end / start balance based of mesures

amitchandak — Mon, 02 Jan 2023 14:45:02 GMT

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

Re: end / start balance based of mesures

ReutAtias12 — Mon, 02 Jan 2023 15:04:32 GMT

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

Re: end / start balance based of mesures

v-yueyunzh-msft — Tue, 03 Jan 2023 03:32:30 GMT

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