How to create a dynamic first date of each period?

[Gruther]

Hi

I'm currently building a PowerBI stock dashboard. I've set up the model & relationships, including automatic updates to retrieve price data of stocks from Yahoo Finance. Now calculating measures to show the analysis I want on my dashboard and have got stuck!

See below extract of a few rows of the data table. Essentially what I want to do here is create a measure that will show what the opening price was on the first trading day of the year (I'll then use this with variables to calcualte the performance over certain time periods).

The problem I have is that due to public holidays/bank holidays in the US/UK, the first trading day isn't the same each year (i.e. I can't just automatically calculate based on a YEAR(TODAY(),1,1) basis). There's multiple years in the data, so again I can't just do a basic MIN of the date either as that would pull through the price from the first year of my data. Tried using variables to create something dynamic, but can't quite figure this one out. Any help appreciated.

Thanks in advance for any help!

Regards.

[Gruther]

@rsbin Fantastic, many thanks for your help! 😀

[rsbin · Accepted solution]

@Gruther ,

Please try something like this:

MinDate_ThisYear = Calculate( MIN( YourTable[Date] ),
                     FILTER( YourTable, Year(YourTable[Date] ) = Year(Today() )))

MinDate_LastYear = Calculate( MIN( YourTable[Date] ),
                     FILTER( YourTable, Year(YourTable[Date] ) = Year(Today()-1 )))

etc...

Would highly recommend adding a Date (Calendar) table to your model and create a relationship between your Date Table and your Fact Table.

Hope you can tweak this to your specific requirements.

Regards,