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:

     

     

3 Replies

  • Ritaf1983's avatar
    Ritaf1983
    Super User
    1 year ago

    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.

  • 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.

     

  • Bibiano_Geraldo's avatar
    Bibiano_Geraldo
    Super User
    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: