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

     

     

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

      • mike87's avatar
        mike87
        Frequent Visitor
        8 years ago

        v-jiascu-msft

         

        I have uploaded a couple of images from the data set and the dashboard.

         

        It is simple stock data with closing price and dates. 

         

         

        Norwegian numbers with comma as decimals separators