Forum Discussion
Jerid421
Helper II
2 years agoMake Negative Values Zero at a Rolled Up Level
I am trying develop a measure, OpenQuantityRT, to recreate a business process that is currently being done manually in a spreadsheet. The end user sums up quantities ("In-Stock Materials"; 'SupplyDe...
Jerid421
Helper II
2 years agoMy newest code:
OpenQuantityRT_Test3 =
// Determines if the current context is at the MRPElementCategoryShortName level
VAR __IsElementCategoryLevel =
ISINSCOPE('SupplyDemandItems'[MRPElementCategoryShortName])
// Retrieves the maximum YearWeekNum from the Date Calendar table for the current context, to implement the "Running Total"
VAR __CurrentYearWeek =
MAX('Date Calendar'[YearWeekNum])
// Corrects the SupplyDemandItems quantities by setting them to zero if their cumulative total is negative
VAR __CorrectedSupplyDemandItems =
ADDCOLUMNS(
'SupplyDemandItems',
"CorrectedQuantity",
IF(
CALCULATE(
SUM('SupplyDemandItems'[MRPElementOpenQuantity]),
FILTER(ALL('SupplyDemandItems'), 'SupplyDemandItems'[Material] = EARLIER('SupplyDemandItems'[Material]))
) < 0,
0,
'SupplyDemandItems'[MRPElementOpenQuantity]
)
)
// Calculates the rolling total without negatives for the Material level and up
VAR __RollingTotalNoNegs =
CALCULATE(
SUMX(
__CorrectedSupplyDemandItems,
[CorrectedQuantity]
),
FILTER(
ALL('Date Calendar'),
'Date Calendar'[YearWeekNum] <= __CurrentYearWeek
)
)
// Calculates the regular rolling total for the ElementCategory level
VAR __RegularRollingTotal =
CALCULATE(
SUM('SupplyDemandItems'[MRPElementOpenQuantity]),
FILTER(
ALL('Date Calendar'),
'Date Calendar'[YearWeekNum] <= __CurrentYearWeek
)
)
// Returns the appropriate rolling total based on the current context level
RETURN
IF(
__IsElementCategoryLevel,
__RegularRollingTotal,
MAX(__RollingTotalNoNegs, 0)
)