Forum Discussion

mike87's avatar
mike87
Frequent Visitor
8 years ago
Solved

Calculating changes on daily data

Hi, 

 

I am trying to create a measure that calculates the daily change for a stock price. I have tried to use LASTDATE function, but for some wierd reason, it turns out December 31th 2017 instead of the date today.  The MAX function turns out the same result, but MAXX gives me the correct one (November 2nd 2017). However, MAXX is formatted as text, and I am not able to format it back to date. 

 

The data set does not contain any rows after november 2nd. I presume that LASTDATE for some reason interpret the year part of the date instead of day. 

 

I will add new data every day, and I want to create a measure that I can show in a card visualisation with daily changes. Either actual changes or percentage. Anyone any idea of how to sort out the lastdate?

 

The data is structured like this:

Column 1: Stock price
Column 2: Date

Need Help
  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    8 years ago

    Hi mike87

     

    Try this MEASURE.
    File attached here as well

    PriceChange =
VAR LastPirce =
    CALCULATE (
        VALUES ( Table1[Stock Price] ),
        FILTER ( Table1, Table1[Date] = MAX ( Table1[Date] ) )
    )
VAR SecondLastDate =
    CALCULATE (
        MAX ( Table1[Date] ),
        FILTER ( Table1, Table1[Date] < MAX ( Table1[Date] ) )
    )
RETURN
    LastPirce
        - CALCULATE ( VALUES ( Table1[Stock Price] ), Table1[Date] = SecondLastDate )

     

     

6 Replies

  • v-jiascu-msft's avatar
    v-jiascu-msft
    Microsoft Employee
    8 years ago

    Hi mike87,

     

    How did you try it? They are working fine in my test. You'd better create a new date table to work with time intelligence functions.

    calendar =
CALENDAR ( DATE ( 2017, 1, 1 ), DATE ( 2017, 12, 31 ) )

    The daily change formula could be like this:

    DailyChange =
VAR yesterday =
    CALCULATE ( MIN ( 'table'[Stock Price] ), PREVIOUSDAY ( 'calendar'[date] ) )
RETURN
    DIVIDE ( yesterday, MIN ( 'table'[Stock Price] ), 0 )

     

    Best Regards!

    Dale

    • mike87's avatar
      mike87
      Frequent Visitor
      8 years ago

      Thank you for your reply Dale.

       

      I haven't used variables that much yet. I do not get the correct numbers, but it seems as the time intelligence function actually work with your approach.

       

      Do you get the correct change? 

       

      • v-jiascu-msft's avatar
        v-jiascu-msft
        Microsoft Employee
        8 years ago

        Hi mike87,

         

        Yes, I usually use it in such scenario. That could depend on your data model and the visual you create. Can you share your pbix file?

         

        Best Regards!

        Dale