Get date of latest value drop

[Tie-Tie]

Hi,

I have a two tables with following columns:

1.  StockPrices (containts a dataset with stock prices update every day)
   1. Date
   2. StockID
   3. Stock Value (e.g. € 53,89)
   4. Trend (compares how much the stock prices differs from previous day; e.g. -1,2%)
2. Stock
   1. StockID
   2. Company name

I'm interested to visualise the latest date of when a stock started to drop compared to day before.

Example (let's say that Microsoft has StockID = 1) StockPrices table:

1     19/05/2018 00:00:00    100         0.03
2     20/05/2018 00:00:00    99.95     -0.05
3     21/05/2018 00:00:00    99.93     -0.02
4     22/05/2018 00:00:00    99.92     -0.01
5     23/05/2018 00:00:00    112,90     0.23

I would need "20/05/2018 00:00:00" as an answer

I thought of a measure to calculate the maximum date of StockPrices with a filter on StockName and where previous (EARLIER) trend was positive and current trend negative; however EARLIER function is not possible for Measures.

Any other way to solve this?

Thank you.

Best,

[v-piga-msft]

Hi @Tie-Tie,

Have you solved your problem by the solution provided by @CoreyP?

If you have solved, only thing that you'll have to notice, just always accept the replies making sense as solution to your question so that people who may have the same question can get the solution directly.

If you still need help, please feel free to ask.

Best Regards,

Cherry

[CoreyP · Accepted solution]

Would something like this work?

m.Firstdate = FIRSTDATE( 'StockPrices'[Date] )



FirstDate w Neg Trend = 

CALCULATE ( [m.Firstdate] , 'StockPrices'[Trend] < 0 )