Change value in colum of stock prices depending on slicer date

[DoriD]

I have a data set with stock prices:

The first column contains the date, second the company and third the stock price. 

Furthermore, I have a slicer for the date (between option) and a slicer for the company. 

I want to show in the desktop a table (Matrix) with Date values as rows (based on the slicer) and columns with companies (based on another slicer).

The values I want to have in that matrix are the changes in stock price compared to the stock price on the earlierst date of the slicer:
For each date and company in the table the Stock price on the date divided by the stock price on the earliest date of the slicer minus 1. 

I have tried multiple attempts by defining measures but without success so far. If anyone has a tip or idea that would be greatly appreciated. 

[DoriD]

Hello,

Many thanks for your help and effort! Unfortunately your proposal does not match my preferred outcome. In your specific example I would like that the all dates 'x' between 19-01 and 18-02 are shown (per company) with stock change value equal to stock price on date x divided by stock price on date 19-01 minus 1. In that way I could eventually show a graph of the relative stock price development in the selected date range and compare comA and comB. Much appreciated of you could let me know any ideas you might have. Thanks!

[samratpbi · Accepted solution]

Hi @DoriD Thanks, based on your initial query, it seems you were looking for the difference between minimum and maximum dates. However you can also daily change in many ways. One way is to use visual calculation.

First, bring the Min Stock Price measure in a line chart and Date in x-axis.

Min Stock Price =

CALCULATE(

    MAX(StockPrice[StockPrice]),

    StockPrice[Date] = MIN(StockPrice[Date])

)

Then open the visual calculation:

by default, it opens the PREVIOUS function on the measure. But update that measure a little:

Versus previous =

IF(

    ISBLANK(PREVIOUS([Date])),

    BLANK(),

    DIVIDE( [Min Stock Price] / PREVIOUS([Min Stock Price]), 1) - 1

)

You may hide your main measure Min Stock Price.

This measure basically hiding the current date value and showing difference from next day onwards.

To show the value in percentages, you also need to do bit of formatting of your visual calculation measure.  This visual calc formatting option comes under Format -> Properties

Now below is the output:

Since for this example, I took dummy data, hence line mostly looks flat, but this should work I thik.

If this helps to resolve your problem, then please mark it as solution.

Thanks - Samrat

[DoriD]

Thank you very much. I wasn't aware of the in visual calculations. It worked by replacing in your formula 'previous' with 'first'.

[samratpbi]

Hi @DoriD , I first created a dummy data for this like below:

Then created 2 simple measures:

Min Stock Price =

CALCULATE(

    MAX(StockPrice[StockPrice]),

    StockPrice[Date] = MIN(StockPrice[Date])

)

Max Stock Price =

CALCULATE(

    MAX(StockPrice[StockPrice]),

    StockPrice[Date] = MAX(StockPrice[Date])

)

then the measure to calculate the difference:

Stock Price Change =

DIVIDE([Max Stock Price], [Min Stock Price], 0) - 1

Now, in the visual, added Date slicer and added Company slicer

Hope this helps.

If this helps to resolve your problem, then please mark it as solution.

Thanks - Samrat