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 )
        )
Zubair_Muhammad's avatar
Zubair_Muhammad
Community 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.