Forum Discussion

pcowman1's avatar
pcowman1
Helper I
7 years ago
Solved

Row - Calculate Difference from Previous Row

I found this on another post and I think it might be the lead I need but if someone knows another way I'd greatly appreciate it. I am trying to find out if there is a box number missing from a set of...
Need Help
  • v-piga-msft's avatar
    v-piga-msft
    7 years ago

    Hi pcowman1 ,

    We could achieve your desired output with the Dax expression.

    Firstly, you need create an Index column in Query Editor and then create the calculated column with the formula below.

    Column =
VAR a =
    'Table1'[Box No_]
        - CALCULATE (
            MAX ( 'Table1'[Box No_] ),
            FILTER ( 'Table1', 'Table1'[Index] = EARLIER ( Table1[Index] ) - 1 )
        )
VAR b =
    CALCULATE ( MAX ( 'Table1'[Box No_] ), 'Table1'[Index] = 1 )
RETURN
    IF ( b = 1, "No", IF ( a > 1, "Yes", "No" ) )

    Here is the output.

    Hope this can help you!

    In addition, if you have questions with other topic, please create a new thread with a new topic so that the community members who have the same question will find the solution directly.

    Best Regards,

    Cherry

     

pcowman1's avatar
pcowman1
Helper I
7 years ago

This is what I'm looking for. In Excel it's this formula: =IF(E2=1,"No",IF((E2-E1)>1,"Yes","No"))

 

I have the information sorted by Item, Production Date, Lot, Pallet, Box. I added an Index column in Power BI hoping to use it and do 'when the index is one less than the current index is the box number also one less' - but in M or DAX.

pcowman1's avatar
pcowman1
Helper I
7 years ago

I have an additional question with this one - Does anyone know Python visuals? I haven't tried it yet. I used to write Python. Maybe this could be done that way now that they're available as part of the PBI Desktop.