Forum Discussion

SSS's avatar
SSS
Icon for Helper I rankHelper 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 )
        )
Ashish_Mathur's avatar
Ashish_Mathur
Icon for Super User rankSuper User
8 years ago

Hi SSS,

 

If you have a calendar table and there is a relatioship from the date column of your base data table to the date column of the calendar table, then you can use this calculated field formula

 

=CALCULATE(SUM(Data[Orders]),PREVIOUSDAY(Calendar{Date]))-SUM(Data[Orders])

  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    Icon for Community Champion rankCommunity Champion
    7 years ago

    Anonymous

     

    You can use one of these...I believe

     

    Days from Next Date =
VAR Next_Date =
    MINX (
        TOPN (
            1,
            FILTER ( Table1, [Key] = EARLIER ( [Key] ) && [Date] > EARLIER ( [Date] ) ),
            [Date], ASC
        ),
        [Date]
    )
RETURN
    DATEDIFF ( [Date], Next_Date, DAY )

    OR

     

    Days from Previous Date =
VAR Previous_Date =
    MINX (
        TOPN (
            1,
            FILTER ( Table1, [Key] = EARLIER ( [Key] ) && [Date] < EARLIER ( [Date] ) ),
            [Date], DESC
        ),
        [Date]
    )
RETURN
    DATEDIFF ( Previous_Date, [Date], DAY )
    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Hi Zubair_Muhammad,

       

      Thanks a lot!

       

      The first one result a strange column, but the second works when I compared with Excel result.

       

      Now, I'll proceed to tests accessing real data from the database.

       

      Dax has some mysteries to me, yet. I didn't know the VAR concept or the possibility to call previous or next records in an instruction.

       

      Thanks a lot again.