Comparing next row data and calculating MOD

[Anonymous]

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/grxqnjtmyczhbdl/Sample%20file.pbix?dl=0

Here is my Code

Column = Var CurentPID =Sheet1[Product ID]
Var NextPID=Sheet1[Product ID]+1
Var CurrentNumber =Sheet1[Number]
Var NextNumber=Sheet1[Number]+1

Return
If(Sheet1[Product ID]=LOOKUPVALUE(Sheet1[Product ID],Sheet1[Index],Sheet1[Index]+1),
if(or(CurrentNumber=1,NextNumber=1),"NA",
if(Mod(CurrentNumber,NextNumber)=0,"OK","FALSE"))
)

My data:

Product ID	Number
11111	10000
11111	1000
11111	1000
11111	1
22222	500
22222	100
22222	1
33333	1

My output with Excel formula =if(B2=1,"na"IF(and(B2=1,B3=1),"na",IF(MOD(B2,B3)=0,"OK","False")))

((However now in Power BI I also want to put a check to see if the product ID in the next cell is same, if not same then if number is 1 then NA))

Product ID	Number	Output
11111	10000	OK
11111	1000	OK
11111	1000	OK
11111	1	na
22222	500	OK
22222	100	OK
22222	1	na
33333	1	na

Any help is appreciated!

Thanks,

Tejaswi

[jdbuchanan71 · Accepted solution]

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