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 )
        )
tomislav_mi's avatar
tomislav_mi
Icon for Helper II rankHelper II
6 years ago

Hey guys,

I encountered one problem I simply can't solve. If anyone can help me out I would be extra grateful!

I want to calculate the difference between two rows, but after I import data to the data model, the sort order is mixed up (see below: Report Date and Account Name) so one of my formulas that could work if the sort order is correct doesn't work.

The table looks like this and the Change column like this is my goal.

Account NameMRRReport DateChange
a1001/31/2017 0:00100
a1002/28/2017 0:000
a1504/30/2017 0:00-30
a1005/31/2017 0:00-50
b137/31/2019 0:0013
b138/31/2019 0:000
c57/31/2019 0:005
a1006/30/2017 0:000
a1007/31/2017 0:000
a1008/31/2017 0:000
a1009/30/2017 0:000
a10010/31/2017 0:000
a1803/31/2017 0:0080


You can notice that the account doesn't have the correct order either by the report date. 

If there is any way how to solve it (maybe as a measure? or maybe is there a way how to correctly import data?)
I would be so so grateful.

All the best,

Tomislav

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Icon for Super User rankSuper User
    6 years ago

    Hi,

    You may download my PBI file from here.

    Hope this helps.

    • tomislav_mi's avatar
      tomislav_mi
      Icon for Helper II rankHelper II
      6 years ago

      Thank you Ashish_Mathur !

      Works amazing!

      Thank you so much.

      All the best from Croatia!

      Tomislav

      • Ashish_Mathur's avatar
        Ashish_Mathur
        Icon for Super User rankSuper User
        6 years ago

        You are welcome.

    • mbelal's avatar
      mbelal
      Regular Visitor
      5 years ago

      Hi, I have almost the same data but the column MRR includes blanks, I appreciate your advice to let me know what the formula will look like? i need if the column value is blank to take the earliest value found instead of calculating based on zeros instead of balnks. Thanks.

      • Ashish_Mathur's avatar
        Ashish_Mathur
        Icon for Super User rankSuper User
        5 years ago

        Hi,

        Refer to column1 of the image.  You will see that the result in the 5th row is not 100-0=100 but 100-180=-80.  You may download my PBI file from here.

        Hope this helps.