Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Calcualte Difference/% Change between two rows.

Hi there,

 

I have the following data:

 

Date  SPX Price    Volume

7/9/20203152.054829020000
7/8/20203169.944927700000
7/7/20203145.324563700000
7/6/20203179.724736450000
7/2/20203130.014190830000
7/1/20203115.864443130000
6/30/20203100.294696280000
6/29/20203053.244462770000
6/26/20203009.058098120000
6/25/20203083.764815420000
6/24/20203050.335587200000
6/23/20203131.294704830000
6/22/20203117.864665380000
6/19/20203097.748327780000
6/18/20203115.344429030000
6/17/20203113.494549390000
6/16/20203124.745829240000
6/15/20203066.595740660000
6/12/20203041.315832250000
6/11/20203002.17018890000
6/10/20203190.146570840000
6/9/20203207.186382620000
6/8/20203232.398437380000

 

I want to add a third column that calculates the % change in Price between two adjacent dates. Is this possible? Thanks.

Need Help
  • amitchandak's avatar
    amitchandak
    6 years ago

    calerof , the correct version. Value replacement issue in my formula

    Last Day Non Continuous = CALCULATE([sales],filter(ALLSELECTED('Date'),'Date'[Date] =MAXX(FILTER(ALLSELECTED('Date'),'Date'[Date]<max('Date'[Date])),'Date'[Date])))

13 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    6 years ago

    Anonymous ,

    As a new colum

    maxx(filter(table, [date] =earlier([Date])-1),[SPX Price])

     

    As a measure

    This Day = CALCULATE(max([SPX Price]), FILTER(ALL('Date'),'Date'[Date]=max('Date'[Date])))
    This Day = CALCULATE(max([SPX Price]), previousday('Date'[Date]))

    • calerof's avatar
      calerof
      Impactful Individual
      6 years ago

      Hi amitchandak,

       

      Your solution doesn't calculate the previous price if there is a gap between current date and the previous day of more than one day:

       

    • amitchandak's avatar
      amitchandak
      Super User
      6 years ago

      Anonymous ,  

       

      New columns if dates are not continous. In case date are continous the first formula I provided should be user. Earlier is powerful but costly. So should reduce the scope. 


      last Date = maxx(filter(table, [date] <earlier([Date])-1),[[date]])
      last Value = maxx(filter(table, [date] =earlier(last Date)),[SPX Price])

       

      Measure Non continous 

      Last Day Non Continous = CALCULATE(max([SPX Price]),filter(all('Date'),'Date'[Date] =MAXX(FILTER(all('Date'),'Date'[Date]<max('Date'[Date])),'Date'[Date])))

      • calerof's avatar
        calerof
        Impactful Individual
        6 years ago

        amitchandak,

        I have a problem with the last expression of the formula: "table[Date]", it doesn't fit, as the rest of the elemtents are calling the Date table and this last one is the date in the fact table.

        F

         

  • az38's avatar
    az38
    Community Champion
    6 years ago

    Hi Anonymous 

    try a measure like

    Measure = 
var _prevDate = CALCULATE(MAX(Table[Date]), Table[Date] < MAX(Table[Date]))
var _prevPrice = CALCULATE(MAX(Table[SPX Price]), Table[Date] = _prevDate )
RETURN
DIVIDE(MAX(Table[SPX Price]), _prevPrice)

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      "A function 'MAX' has been used in a True/False expression that is used as a table filter expression. This is not allowed."

       

      That is the error that comes with the measure.

      • az38's avatar
        az38
        Community Champion
        6 years ago

        Anonymous 

        yes, sorry

        Measure = 
var _curDate = MAX(Table[Date]) 
var _prevDate = CALCULATE(MAX(Table[Date]), Table[Date] < _curDate )
var _prevPrice = CALCULATE(MAX(Table[SPX Price]), Table[Date] = _prevDate )
RETURN
DIVIDE(MAX(Table[SPX Price]), _prevPrice)