Forum Discussion
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...
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" )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" )
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"
)HI jdbuchanan71 ,
I found the below issue when I added this code in a actual file.
When I used this in my actual data , it should appear false if the mod is not zero however it still shows OK.
Can you please help me?
For example : Product ID 44444 , Number is 110 and the next number is 12, the Mod of these two numbers 110/12 is non zero still the output shows OK, Could you please help tweak your code?
Thanks,
Tejaswi
Anonymous
Try changing it to this
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" )The MOD was off because the row index was not getting picked up.