topic Re: DAX previous row value in calculation in DAX Commands and Tips

DAX previous row value in calculation

PKPK90 — Fri, 23 Aug 2024 08:44:26 GMT

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=eka0btbauusi5hvbetovb9xkp&st=jgrzqlqb&dl=0
    PBX with data example:
    https://www.dropbox.com/scl/fi/immyfhzkf7xz5bloc3jew/RunningSum.pbix?rlkey=5vquqasquw7h92vh5kg7evjhn&st=54hrp6pb&dl=0

Re: DAX previous row value in calculation

bhanu_gautam — Fri, 23 Aug 2024 09:18:26 GMT

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

Re: DAX previous row value in calculation

PKPK90 — Fri, 23 Aug 2024 09:36:34 GMT

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.

Re: DAX previous row value in calculation

Anonymous — Tue, 27 Aug 2024 02:06:35 GMT

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