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 m...
Need Help
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