Get % between current and prev rows

[NaiduNaidu]

Hi All,

I have below data and I need to get % diff for current row and previous row and also show first row as 0 if there is no previous row.

Here my year column is in string datatype.

[Bibiano_Geraldo · Accepted solution]

Hi @NaiduNaidu ,

Ypu can achieve your goal following these steps:
1- Create a new calculated column for sortYear using this DAX:

SortYear = VALUE(SUBSTITUTE('Table'[Year], "F", ""))

2- Now create a new measure for % Diff by this DAX:

% Diff = 
VAR CurrentSales = MAX('Table'[Sales])
VAR CurrentYear = MAX('Table'[SortYear])
VAR PreviousSales = 
    CALCULATE(
        MAX('Table'[Sales]),
        FILTER(
            ALL('Table'),
            'Table'[SortYear] = CurrentYear - 1
        )
    )
RETURN
IF(
    ISBLANK(PreviousSales),
    0,
    DIVIDE(CurrentSales - PreviousSales, PreviousSales)
)

3- Now drag and drop the % Diff measure for your Matrix, your result will look like this:

[Ilya_K]

You can try "Visual calculations".

For that select "..." on the table right top corner, select "Visual calculations" and "Versus Previous".
In popped up formula, indicate "Sales" column.

[Ritaf1983]

Hi @NaiduNaidu 
You can use Offset function to get a desired result :

Previous row sales diff %=

var sales_= sum('Table'[Sales])

var prev = CALCULATE(sum('Table'[Sales]), OFFSET(-1, ALLSELECTED('Table'[Year]), ORDERBY ('Table'[Year], ASC)) )+0

RETURN

DIVIDE((sales_-prev),prev)

The pbix is attached

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