Forum Discussion

ConnorH's avatar
ConnorH
Icon for Microsoft Employee rankMicrosoft Employee
5 years ago
Solved

Help with YoY % change

Hi,

 

I have a table below. I am trying to take the [gross running YTD] column and find the percentage growth to the next value.

So for example the value 994143 wont have a percentage growth because there was no previous value but the 3589095 will show the % groth from the 994143 to the 3589095 and the next value will show growth from 3589095 to the 6379539.

 

  • ConnorH's avatar
    ConnorH
    5 years ago

    Just got this problem figured out so I am sharing the solution in case someone comes accross this thread with a similar problem.

    Var prevValue = CALCULATE(MAX('Table'[Gross Revenue YTD]), FILTER('Table', 'Table'[Gross Revenue YTD] < EARLIER('Table'[Gross Revenue YTD])))
    Var currentValue = 'Table'[Gross Revenue YTD]
    var difference = currentValue-prevValue
    return DIVIDE(difference,prevValue)

3 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    5 years ago

    ConnorH , New columns

     

    QTR Year = [Year]*100 + [quarter]
    Qtr Rank = RANKX(all('Date'),'Date'[QTR Year],,ASC,Dense)

    Last Value =
    var _max = maxx(filter(Table, [Qtr Rank] <= earlier([Qtr Rank]) && not(isblank([Gross Running ytd]))),[Qtr Rank])
    var _last = usmx(filter(Table, [Qtr Rank] =_max),[Gross Running ytd])
    return
    divide([Gross Running ytd] -_max, _max)

     

    if you need measures , then make sure Qtr and Year and Qtr Year and Rank are in a separate table, say Date


    This Qtr = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Qtr Rank]=max('Date'[Qtr Rank])))
    Last Qtr = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Qtr Rank]=max('Date'[Qtr Rank])-4)) // here there is diff of 4

    • ConnorH's avatar
      ConnorH
      Icon for Microsoft Employee rankMicrosoft Employee
      5 years ago

      I have just done the following steps and am not receiving the correct values.

      Here is a table to help clarify what I am struggling to do:

       

  • ConnorH's avatar
    ConnorH
    Icon for Microsoft Employee rankMicrosoft Employee
    5 years ago

    Just got this problem figured out so I am sharing the solution in case someone comes accross this thread with a similar problem.

    Var prevValue = CALCULATE(MAX('Table'[Gross Revenue YTD]), FILTER('Table', 'Table'[Gross Revenue YTD] < EARLIER('Table'[Gross Revenue YTD])))
    Var currentValue = 'Table'[Gross Revenue YTD]
    var difference = currentValue-prevValue
    return DIVIDE(difference,prevValue)