Row - Calculate Difference from Previous Row

[pcowman1]

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 numbers. There are more fields in play but I've done Order By in my query and just need to compare if the box number in the current row is 1 different from the previous  (1, 2, 3, 4, 5 not 1, 2, 3, 5, 6). I've changed this already to the two fields I believe are needed. 

Index.1 = Index Row

Box No_ = Number to be compared to previous row.

Difference =

IF(

'Box Sequence'[Index.1] = 0,

FALSE(),

LOOKUPVALUE(

'Box Sequence'[Box No_],

'Box Sequence'[Index.1],

'Box Sequence'[Index.1]-1)

)

[v-piga-msft]

Hi @pcowman1 ,

I still have  a little confused about your sceanrio.

If it is convenient, could you share your data sample and your desired output so that we could help further on it.

Best Regards,

Cherry

[pcowman1]

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.

[v-piga-msft · Accepted solution]

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]

Thank you! That works. I'm going to use it in conjunction with my other 'solution' which was to do a table which did Sum(Quantity) and MAX (Box) and then subtracted one from the other. That gave me which ones to check and then this gives me a quicker reference as to which box is missing. I can put a highlight on the cell. Much appreciated.

[pcowman1]

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.