Forum Discussion

Aron_Moore's avatar
Aron_Moore
Solution Specialist
8 years ago
Solved

DAX Help - Compare to nonexistent row

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!

Need Help
  • v-ljerr-msft's avatar
    v-ljerr-msft
    8 years ago

    Hi Aron_Moore,

     

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

    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

2 Replies

  • v-ljerr-msft's avatar
    v-ljerr-msft
    Microsoft Employee
    8 years ago

    Hi Aron_Moore,

     

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

    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