Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Comparing next row data and calculating MOD

Hi,   I am trying to compare the value of Product ID with the next cell in the table and performing some If conditions. But my logic doesn't work.     link to my file-- https://www.dropbox.com/s...
Need Help
  • jdbuchanan71's avatar
    jdbuchanan71
    6 years ago

    Hello Anonymous 

    This worked for me on your sample data, give it a try.

    Column = 
VAR _PreviousAmount = 
    CALCULATE(
        SUM ( Sheet1[Number] ),
        ALLEXCEPT ( Sheet1, Sheet1[Product ID] ),
        Sheet1[Index] = Sheet1[Index] - 1)
VAR _MOD = MOD(_PreviousAmount,Sheet1[Number])
RETURN
SWITCH ( 
    TRUE(),
    Sheet1[Number] = 1, "na",
    _MOD = 0, "OK",
    "False"
)
  • jdbuchanan71's avatar
    jdbuchanan71
    6 years ago

    Gotcha, just change it to this:

    Column = 
VAR _Index = 'Sheet1'[Index]
VAR _NextAmount = 
    CALCULATE(
        SUM ( 'Sheet1'[Number] ),
        ALLEXCEPT ( 'Sheet1', 'Sheet1'[Product ID] ),
        'Sheet1'[Index] = _Index + 1)
VAR _MOD = IF ( ISBLANK(_NextAmount),0,MOD('Sheet1'[Number],_NextAmount))
Return
SWITCH ( 
    TRUE(),
    'Sheet1'[Number] = 1, "na",
    _MOD = 0, "OK",
    "False"
)