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

This is the code I am currently working with:

OpenQuantityRT_AllScopes = 
VAR __IsMaterialLevel =
    ISINSCOPE('SupplyDemandItems'[Material])
VAR __IsElementCategoryLevel =
    ISINSCOPE('SupplyDemandItems'[MRPElementCategoryShortName])
VAR __CurrentYearWeek =
    MAX('Date Calendar'[YearWeekNum])
VAR __RegularRollingTotal =
    CALCULATE(
        SUM('SupplyDemandItems'[MRPElementOpenQuantity]),
        FILTER(
            ALL('Date Calendar'),
            'Date Calendar'[YearWeekNum] <= __CurrentYearWeek
        )
    )
VAR __MaterialLevelandUpRollingTotal =
    IF(
        __IsMaterialLevel,
        CALCULATE(
            __RegularRollingTotal,
            ALLEXCEPT('SupplyDemandItems', 'SupplyDemandItems'[Material])
        ),
        BLANK()
    )
RETURN
    IF(
        __IsElementCategoryLevel,
        __RegularRollingTotal, // Detailed level shows actuals, including negatives
        MAX(__MaterialLevelandUpRollingTotal, 0) // Material level and up zero out if negative
    )
  • 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)
        )