Forum Discussion

sukram1's avatar
sukram1
Frequent Visitor
3 years ago

Difference from previous row based on measure

Hi,

I am trying to create the difference from the previous row based on a measure. I have the following initial situation:

  • The first two columns are attributes from the dataset.
  • The first measure calculates the ratio of premium1 to premium 2.
  • The next two columns are also Measures, which calculates the difference to the previous rows. For this I use a column with an index (running number), which was inserted in the dataset as an additional column.
  • The last measure again determines the ratio between the two previous columns.
  • The orange colored column is what I want to determine.

I want to determine the difference to the previous column based on the measure. I have already tried to use RANKX to classify the measure accordingly (like an index) and to calculate the difference based on this classification. But this does not give any result.

Is it at all possible to calculate the difference to the value from the previous row based on a measure?

 

Many thanks in advance!

 

Need Help

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi  sukram1 ,

     

    Here are the steps you can follow：

    1. Create measure.

    Difference =
var _current=
SUMX(
    FILTER(ALL('Table'),'Table'[Index]=MAX('Table'[Index])),[Diff_P1/Diff_P2])
var _last=
SUMX(
    FILTER(ALL('Table'),'Table'[Index]=MAX('Table'[Index])-1),[Diff_P1/Diff_P2])
return
IF(
    _last=BLANK(),BLANK(),
_current - _last)

    2. Result:

    If I have misunderstand your meaning, please contact me and provide your desired output.

     

    Best Regards,

    Liu Yang

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

    • sukram1's avatar
      sukram1
      Frequent Visitor
      3 years ago

      HI Anonymous,

      Thank you very much for your help and your solution. You got it right and it is exactly what I am looking for!

       

      Unfortunately, the solution does not work with my data. I suspect that I have created the measure for the difference incorrectly. Here is my result:

      This is how I created the Measures, which determines the difference between the two premiums:

      Diff_P1 = 
VAR currIndex = 
    MAX(Test_Table[Index])
VAR vP1 =
    CALCULATE(
            SUM(Test_Table[Premium_1]);
            FILTER(ALLSELECTED(Test_Table); [Index] <= currIndex)
    )
RETURN
    vP1
      Diff_P2 = 
VAR currIndex = 
    MAX(Test_Table[Index])
VAR vP2 =
    CALCULATE(
            SUM(Test_Table[Premium_2]);
            FILTER(ALLSELECTED(Test_Table); [Index] <= currIndex)
    )
RETURN
    vP2

       Somehow the solution for me ignores to consider the measure Diff_P1/Diff_P2. Therefore, the error can only be in my previous data steps.

      Best regards,
      Markus