Forum Discussion

NaiduNaidu's avatar
NaiduNaidu
New Member
1 year ago
Solved

Get % between current and prev rows

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.  
Need Help
  • Bibiano_Geraldo's avatar
    Bibiano_Geraldo
    1 year ago

    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's avatar
Ilya_K
Frequent Visitor
1 year ago

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.