Forum Discussion

SSS's avatar
SSS
Helper I
8 years ago
Solved

Difference between two rows

Hi,   I need help in order to solve a problem trying to calculate the differences between two rows in Power BI.   My tables look like: Date              Orders             Index 01/01/2017   85...
Need Help
  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    8 years ago

    Hi SSS

     

    Using DAX you can add this calculated column to get desired results

     

    =
VAR NextIndex = Table1[Index] + 1
RETURN
    Table1[Orders]
        - CALCULATE (
            VALUES ( Table1[Orders] ),
            FILTER ( ALL ( Table1 ), Table1[Index] = NextIndex )
        )
Rathan's avatar
Rathan
Frequent Visitor
5 years ago

Hello Guys,

 

I have the same question on how to find the difference between two rows from the dated column. Below is how my table looks.

 

 
Date QuantityDiff
06/01/20201 
06/02/202032
06/03/202063

 

I just want the difference between the quantity column. I have tried multiple queries but it works fine in Import Mode but mine is Direct Query mode. Looks like there are many limitations in Direct Query. Could someone help me in giving some guidance regarding this for DIrect Query?

 

Thanks in advance.

 

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    5 years ago

    Hi,

    You should have a Calendar Table with a relationship from the Date column of your Data Table to the Date column of your Calendar Table.  To your visual, drag the Date column from the Calendar Table.  Write these measures:

    Quantity = sum(Data[Number])

    Quantity on previous day = calculate([quantity],previousday(calendar[date]))

    Diff in quantity = [quantity]-[Quantity on previous day]

    Hope this helps.

    • Rathan's avatar
      Rathan
      Frequent Visitor
      5 years ago

      Thank you. It worked

      • Ashish_Mathur's avatar
        Ashish_Mathur
        Super User
        5 years ago

        You are welcome.  If my reply helped, please mark it as Answer.