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"
)

     

jdbuchanan71's avatar
jdbuchanan71
Super User
6 years ago

This does not match what my results are:

Column = 
VAR _Index = 'Sheet1'[Index]
VAR _PreviousAmount = 
    CALCULATE(
        SUM ( 'Sheet1'[Number] ),
        ALLEXCEPT ( 'Sheet1', 'Sheet1'[Product ID] ),
        'Sheet1'[Index] = _Index - 1)
VAR _MOD = MOD(_PreviousAmount,'Sheet1'[Number])
RETURN
SWITCH ( 
    TRUE(),
    'Sheet1'[Number] = 1, "na",
    _MOD = 0, "OK",
    "False"
)

Anonymous's avatar
Anonymous
Not applicable
6 years ago

jdbuchanan71 ,

 

yes I made a mistake while copy pasting and renaming the the actual field name.  I get the results what you get .

 

However can we have the highest number as false. In you case it shows 12 as false. I want highest number 110 false if the mod is non zero and 12 as OK

 

Appreacite all your kind help!

 

Thanks,

Tejaswi

  • jdbuchanan71's avatar
    jdbuchanan71
    Super User
    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"
)