Dynamically calculate multiple stock returns based on date slicer

[Anonymous]

Hi Power BI Community,

I recently came across an inspiring Power BI dashboard that provides a comprehensive analysis of individual stock performance within the S&P500. I'm eager to create something similar, but I'm facing challenges in automatically sorting the stock returns. To simplify the issue, I've prepared a sample setup that mirrors the problem.

The data is defined as follows: 

I've created a table with the following DAX code:

First_Last_Prices = 
SUMMARIZE (
    FILTER (
        ALL ( Sheet1 ),
        Sheet1[Date] >= MIN ( 'Sheet1'[Date].[Date])
            && Sheet1[Date] <= MAX ( Sheet1[Date].[Date] )
    ),
    Sheet1[ID],
    "First_Price", FIRSTNONBLANK ( Sheet1[PX_LAST], 1 ),
    "Last_Price", LASTNONBLANK ( Sheet1[PX_LAST], 1 )
)

Unfortunately the data in the table with the first and last price isnt changing when the dates in the slicers are changed.

The IDs are linked in model view.

I'm unable to attach the original file here, but you can access the Power BI file pbix and the accompanying data. 

Could you please provide guidance on improving the calculation of returns dynamically based on a date slicer?

Thank you in advance for your help!

[Anonymous]

Hi @Anonymous ,

Based on the information you provided, I understand that the problem you are experiencing is that First_Last_Prices does not change based on the slicer, you can follow these steps below:

1.Add ID table

2.Create relationships

3.Put table ID into Slicer

Final output:

How to Get Your Question Answered Quickly - Microsoft Fabric Community

If I misunderstood you or it does not work, please provide more details with your desired out put and pbix file without privacy information.

^{Best Regards,}

^{Yifan Wang}

^{If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.}

[Anonymous]

Dear Yifan,

Thanks for your response! The values of first_price and last_price still dont respond to changes in the date slicer; it shows the data based on the data since inception ie the first and last date in the dataset. I've added a table with the PX_LAST data from sheet1 based on the last 3 years and the values dont correspond.

Also, I would like to show the first and last price based for all IDs ie the ID slicer should not affect the table with first_price and last price_price.

Regards,

Webko

[amitchandak]

@Anonymous , You can follow approach given in the blog

Latest
https://amitchandak.medium.com/power-bi-get-the-last-latest-value-of-a-category-d0cf2fcf92d0

https://amitchandak.medium.com/power-bi-get-the-sum-of-the-last-latest-value-of-a-category-f1c839ee884e