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

     

kentyler's avatar
kentyler
Solution Sage
6 years ago

I think the standard advice in Power BI is to do these kinds of calculations in Excel. Excel is made for doing things involving adjacent cells and rows. Power BI is not. You can do it in Power BI but its complicated and you end up with slow measures.