Simple Excel task with DAX

[agolumeste]

Please help! How can I use DAX to solve a simple excel task!

I know the values in column A and the first Sum 1000 and need to calculate the Sum for each value in column A

    B           C
1 %       Sum
2 2,0%  1000
3 3,5%   965
4 3,0%   936,05

C3=C2-B2*C2

C4=C3-B3*C3 etc.

Thanks!

[Anonymous]

Is this calculation to be a calculated column or a measure?

[agolumeste]

Calculated column

[amitchandak]

@agolumeste , assume you have sequence in column A that can be used then try like

sumx(filter(Table, [A] =earlier([A]) -1),earlier([C]) - (earlier([C]) *earlier([B])))

or

sumx(filter(Table, [A] =earlier([A]) -1),([C]) - (([C]) *([B])))

or add an index column like

https://stackoverflow.com/questions/45715963/creating-an-index-column-for-power-bi

[agolumeste]

Thanks for help!

I can't use the EARLIER function because I calculate a column that doesn't exist yet.

[Anonymous]

In DAX you can't use recursion. Your formula is recursive. To calculate what you want it's best to use Power Query, where you CAN use recursion. If you really want to do it in DAX, you have to find a closed formula for the calculation. Yes, it's possible and not very hard; you just need some maths. If you can't find it, you can certainly find such a formula via Google or on Wolfram Alpha.