Forum Discussion

Aron_Moore's avatar
Aron_Moore
Icon for Solution Specialist rankSolution 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...
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

v-ljerr-msft's avatar
v-ljerr-msft
Icon for Microsoft Employee rankMicrosoft 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

  • Aron_Moore's avatar
    Aron_Moore
    Icon for Solution Specialist rankSolution Specialist
    8 years ago

    Ah, good catch. That did it.

     

    Thanks!