Forum Discussion

pcowman1's avatar
pcowman1
Icon for Helper I rankHelper 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
Icon for Resident Rockstar rankResident 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
    Icon for Helper I rankHelper 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
      Icon for Resident Rockstar rankResident 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
        Icon for Helper I rankHelper 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
      Icon for Helper I rankHelper 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.