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
------------------------------
Original Message:
Sent: May 02, 2022 03:13 AM
From: Dane Christian Belarmino
Subject: DAX Measure - Value based on previous row
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] ) - 1RETURN CALCULATE ( SUM ( 'Table2'[Potential Movement] ), FILTER ( ALL ( 'Table2' ), 'Table2'[Index] = __prev ) )
------------------------------
Dane Christian Belarmino
Business Analyst`
Lexmark Research & Dev. Corp.
Cebu
9958669554
Original Message:
Sent: Apr 30, 2022 12:16 PM
From: Jacques Le Van
Subject: DAX Measure - Value based on previous row
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
------------------------------