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
    )