Calculating changes on daily data

[mike87]

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

[v-jiascu-msft]

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]

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]

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]

@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

[Zubair_Muhammad · Accepted solution]

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 )

[mike87]

Thank you @Zubair_Muhammad.

That worked perfectly