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 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)
)
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

     

5 Replies

  • 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