DAX previous row value in calculation

[PKPK90]

Hi, am trying to create dax formula based on logic from excel: NORM is what I would like to achieve 
NORM=IF(AND(BM3=1,BL2<0),BL2,IF(AND(BM3=1,BL2>0),IF(BL2+(G3-H3+BD3-BE3)>0,BL2+(G3-H3+BD3-BE3),0),BL2+(G3-H3+BD3-BE3)))

• EXP Value: Each row has an associated EXP value which can be either 1 or 0.

• Previous Row Value: The calculation for the current row should take into account the result from the previous row.

• Conditions Based on EXP:

  • If EXP is 1:
    • If the value from the previous row is negative or zero, the value should remain unchanged.
    • If the value from the previous row is positive, it should be reduced by the value of OutStandingRequirement. However, the result cannot be less than zero; if the subtraction results in a negative value, it should be set to zero.
      
      

      this is my formula without "EXP" logic:

      NORM_2 =

      CALCULATE(

          SUM('Table'[OutstandingReceipt]) - SUM('Table'[OutStandingRequirement]) +

          SUMX(

              FILTER('Table', 'Table'[Ref_OrderNo.1] = "BALANCE"),

              'Table'[OnHandQtyADJ]

          )-

              SUMX(

              FILTER('Table', 'Table'[Ref_OrderNo.1] = "BALANCE"),

              'Table'[SafetyStockADJ]

          ),

          FILTER(

              ALL('Table'),

              'Table'[SiteItem] = EARLIER('Table'[SiteItem]) &&

              'Table'[Index] <= EARLIER('Table'[Index])

          )

      )
      EXCEL file:
      https://www.dropbox.com/scl/fi/tf8ems7baq0sgta29l72j/RunningSUM_.xlsb?rlkey=eka0btbauusi5hvbetovb9xk...
      PBX with data example:
      https://www.dropbox.com/scl/fi/immyfhzkf7xz5bloc3jew/RunningSum.pbix?rlkey=5vquqasquw7h92vh5kg7evjhn...

[bhanu_gautam]

@PKPK90 , You can create a calculated column for Norm using below dax

NORM =
VAR CurrentIndex = 'Table'[Index]
VAR CurrentEXP = 'Table'[EXP]
VAR PreviousNORM =
CALCULATE(
MAX('Table'[NORM]),
FILTER(
'Table',
'Table'[SiteItem] = EARLIER('Table'[SiteItem]) &&
'Table'[Index] < CurrentIndex
)
)
VAR OutstandingReceipt = 'Table'[OutstandingReceipt]
VAR OutStandingRequirement = 'Table'[OutStandingRequirement]
VAR OnHandQtyADJ =
CALCULATE(
SUM('Table'[OnHandQtyADJ]),
FILTER('Table', 'Table'[Ref_OrderNo.1] = "BALANCE")
)
VAR SafetyStockADJ =
CALCULATE(
SUM('Table'[SafetyStockADJ]),
FILTER('Table', 'Table'[Ref_OrderNo.1] = "BALANCE")
)
VAR Calculation = OutstandingReceipt - OutStandingRequirement + OnHandQtyADJ - SafetyStockADJ
VAR Result =
IF(
CurrentEXP = 1,
IF(
PreviousNORM < 0,
PreviousNORM,
IF(
PreviousNORM > 0,
MAX(0, PreviousNORM + Calculation),
PreviousNORM + Calculation
)
),
PreviousNORM + Calculation
)
RETURN
Result

[PKPK90]

Hi, thank you for your reply. In your message, you are referring to the NORM column, which is just an example of what I hope to achieve with the DAX calculation. The NORM column should not be included in the calculation.

[Anonymous]

Hi bhanu_gautam ,thanks for the quick reply, I'll add more.

Hi @PKPK90 ,

Regarding your question, I think DAX cannot meet your needs, you need to use Power Query to do iterative calculations.

Best Regards,
Wenbin Zhou