% Change calculation

[chotu27]

Hi All,

I need  % Change calculation as mentioned below table

[Phil_Seamark]

Hi @chotu27

This calculated column seems to do the trick with your sample data.  I have attached a PBIX model.

% Change = 
VAR ThisValue = 'Table1'[Sales]
VAR LastValue = MINX(FILTER('Table1','Table1'[Sales] > ThisValue),'Table1'[Sales])
RETURN 
    IF(ISBLANK(LastValue),1,
     - DIVIDE( 
         (LastValue - ThisValue ) , LastValue) )

[chotu27]

@Phil_Seamark Good One !  But Some how it is not working for my dataset

Please check below data.

% change cal =  current visit -previous visit/ previous visit

Suppose for S1051 calculation i s=  Visit 2 - Visit 1/Visit1 which is 80-100/100 =  - 0.20 

I need a calculation like above

[Phil_Seamark · Accepted solution]

Hi @chotu27

I'm doing it as a calculated measure this time.

Measure = 
VAR LastVisit = 
    CALCULATE(
        MAX('Table2'[Visit Name]),
        FILTER(
            ALL('Table2'),
            'Table2'[Subject Number] = MAX('Table2'[Subject Number]) && 'Table2'[Visit Name] < MAX('Table2'[Visit Name])
            ))

VAR Visit1 = 
    CALCULATE(
        SUM('Table2'[Score]),
        FILTER(
            ALL('Table2'),
            'Table2'[Subject Number] = MAX('Table2'[Subject Number]) &&
            'Table2'[Visit Name] = LastVisit
            )
        )
VAR Visit2 = SUM('Table2'[Score])        
RETURN DIVIDE(
            Visit2 - Visit1 ,
            Visit1
)  

[chotu27]

@Phil_Seamark  Excellent It got Worked  but some how i think my table in real contains so many columns so that is why it is not working for me.  

Is your calculation works for only 3 columns or more than that in table?

  Table and data view

[Phil_Seamark]

HI @chotu27

When you say it isn't working, do you mean you are expecting a lot more in the final column in your screenshot?

[chotu27]

@Phil_Seamark Thank you very much i got it worked finally . Your solution is smart

There is one more similar calculation i need

I wanted to compare values  with previous row and flag it such as if current value = previous value then put 1 otherwise put 0

look at screen shot below

if visit 2 score is same as visit 1 score then 1 otherwise 0