Forum Discussion

Jerid421's avatar
Jerid421
Icon for Helper II rankHelper II
2 years ago

Make 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...
"DAX help"
"DAX"
Jerid421's avatar
Jerid421
Icon for Helper II rankHelper II
2 years ago

I came at it a different way and I think it's better, but now it doesn't seem to be calculating the rolling total correctly (at all):

OpenQuantityRT =
// 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)
    )