Power BI Exchange

Please login or click SIGN UP FOR FREE to create your PowerBIUG account to join this user group.
 View Only
  • 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
    Business Analyst`
    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
    ------------------------------