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

     

v-piga-msft's avatar
v-piga-msft
Resident Rockstar
7 years ago

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'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.

  • v-piga-msft's avatar
    v-piga-msft
    Resident Rockstar
    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

      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'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.