Forum Discussion
Helper IDAX 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),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:
EXCEL file: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])))
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
- If EXP is 1:
3 Replies
- bhanu_gautam
Super User
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
PKPK90Hi, 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.
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
