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

     

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.