Power BI Exchange

View Only

DAX Measure - Value based on previous row

• 1.  DAX Measure - Value based on previous row

Posted Apr 30, 2022 12:16 PM

Hello,

I am stuck on this problem for days and I hope you can help. I am not an expert yet in DAX and maybe I overlooked a simple solution.

My problem can be summarized as follows:
- My table has an index that I use as ascending (equivalent to a date).
- I calculated a 'Potential Movement' Value, which is a calculated measure that can be positive or negative (and is based on calculations between What-if Parameters and a column of the table)
- This 'Potential Movement' should illustrate how moves my 'Final Value', that starts at 0 with the Index 1 and then should iterate using the Potential Movement value of each row.
- This is where it complicates : This 'Final Value' can only be between 0 and 10. if the Potential Movement should make the final value goes over those thresholds, the Final Value should be 'stuck' by those thresholds.

- Note that those threshold values are calculated with other What If Parameters

It is something easy on excel with a formula based on previous value but my brain does struggle to process the problem on DAX / measures. I hope you can help?

 Index Potential Movement(for calculating next value) Final Value (Expected Output) Comment 1 5 0 Starting Value 2 -2 5 =0+5 3 -4 3 =5-2 4 3 0 =3-4 But Cannot Be <0 So = 0 5 8 3 =0+3 6 8 10 =3+8 But Cannot Be >10 So 10 7 -12 10 Cannot Increase Over 10 8 4 0 =10-12 But Cannot Be <0 So 0

Thanks

------------------------------
Jacques
------------------------------

• 2.  RE: DAX Measure - Value based on previous row

Posted May 02, 2022 03:13 AM

Tried calculating the final value but confused how you actually calculate it.
Index 1  = understandable
Index 2  = seems to be value of the previous index  +  the value before that thus 5 + 0
Index 3 = value of index 1 + value of index 2
Index 4 = suddenly the value of Index 4 +  the value of index 3 but since it is < 0 thus 0 ( this is where I start to get confused)

Anyway, here a sample formula for getting the value of the previous index:

``````Previous Value =
VAR __prev =
MAX ( 'Table2'[Index] ) - 1
RETURN
CALCULATE (
SUM ( 'Table2'[Potential Movement] ),
FILTER ( ALL ( 'Table2' ), 'Table2'[Index] = __prev )
)
``````

------------------------------
Dane Christian Belarmino
Lexmark Research & Dev. Corp.
Cebu
9958669554
------------------------------

• 3.  RE: DAX Measure - Value based on previous row

Posted May 03, 2022 04:08 AM

Hi,

Thanks - I adjusted your formula as my 'Potential Movement' was a calculated measure already, and tried to put together my formula for final value.
However I still have a problem as it tells me there is a circular dependency error now :

`Final Value = `
`VAR _index=MAX('Table'[Index])`
`VAR _prev_value=SUMX ('Table',CALCULATE([Final Value],FILTER ( ALL ( 'Table'),'Table'[Index]=(_index-1))))`
`VAR _movement=SUMX ('Table',CALCULATE([Potential Movement],FILTER ( ALL ( 'Table'),'Table'[Index]=(index))))`
`RETURN`
`IF(MAX('Table'[Index])=1,0,`
`IF((_prev_value+_movement)<0,0,IF((_prev_value+_movement)>10,10,_prev_charge+_movement)))`

------------------------------
Jacques Le Van
------------------------------