Forum Discussion

PKPK90's avatar
PKPK90
Icon for Helper I rankHelper I
2 years ago

DAX previous row value in calculation

  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),...
bhanu_gautam's avatar
bhanu_gautam
Icon for Super User rankSuper User
2 years ago

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's avatar
PKPK90
Icon for Helper I rankHelper I
2 years ago

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's avatar
    Anonymous
    Not applicable
    2 years ago

    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