DAX Help - Compare to nonexistent row

[Aron_Moore]

Hello experts,

Trying to build a model that compares inventory levels period to period. Trouble is, when the level goes from 0 (or blank) to X, it doesn't calculate that as an increase. Tried the +0 trick but doesn't seem to work either. How can I do this?

Current measure:

Delta QTY = CALCULATE(SUM('Monthly SLoc Value'[Total Q])+0,FILTER('Monthly SLoc Value','Monthly SLoc Value'[Date]=MAX('Calendar'[Date])))
            -CALCULATE(SUM('Monthly SLoc Value'[Total Q])+0,FILTER('Monthly SLoc Value','Monthly SLoc Value'[Date]=MIN('Monthly SLoc Value'[Date])))

Results for two different materials. The second which has values each period works, but the first didn't exist period one and thus fails.

Thanks!

[v-ljerr-msft · Accepted solution]

Hi @Aron_Moore,

Could you try the formula below to see if it works in your scenario? 

Delta QTY =
CALCULATE (
    SUM ( 'Monthly SLoc Value'[Total Q] ) + 0,
    FILTER (
        'Monthly SLoc Value',
        'Monthly SLoc Value'[Date] = MAX ( 'Calendar'[Date] )
    )
)
    - CALCULATE (
        SUM ( 'Monthly SLoc Value'[Total Q] ) + 0,
        FILTER (
            'Monthly SLoc Value',
            'Monthly SLoc Value'[Date] = MIN ( 'Calendar'[Date] )
        )
    )

Regards

[Aron_Moore]

Ah, good catch. That did it.

Thanks!