Matching & calculating list values from the current year, that occurred in previous years

[Martynas_BI]

Hi,

Context:

I am working with pubclicly available data regarding Invesco‘s QQQ exchange tranded fund (ETF), which comprises of 100 publicly listed companies.

Challenge:

I want to see how many of the companies included in the year 2022 list were present in the previous years (going back 10yrs to 2013).

For example, Apple has been in the QQQ for all of the 10 years, whereas AirBnB was only included in 2022.

This is the link to the raw dataset I gathered:

https://docs.google.com/spreadsheets/d/1Sa9xva8HVuW7178WPJPV-nv0ClrqMwDM19SokaIztMM/edit#gid=0

I have manipulated it in Power Query, which looks like this:

My attempts that did not work:

I have tried several ways, for example, in the report view I created a slicer for the year and chose only ‘2022’, and I made this slicer not to affect the bar chart. Then in the other slicer I have the ‘ticker’ values, which represent all of the companies that were included in 2022. I thought by selecting all the values in the ticker slicer regarding 2022, it will naturally represent the count in the bar matching the ticker values for the previous years.

I have also looked at previous Power BI community threads, commonly found the LOOKUPVALUE function as a solution, however, it did not work for me.

Outcome/result wanted:

Ideally I would like to create a column chart showing how many companies of the 2022 list were also present in previous years.

Many thanks in advance

[Ashish_Mathur]

Hi,

I am not clear about the chart that you want.  What do you want to see on the X-axis of the column chart?

[v-shex-msft · Accepted solution]

HI @Martynas_BI,

You can try to use the following measure formula to calculate the previous year records based on selected date:

formula =
VAR selected =
    MAX ( NewTable[Date] )
RETURN
    CALCULATE (
        'expression',
        FILTER (
            ALLSELECTED ( Table ),
            YEAR ( [Date] )
                >= YEAR ( selected ) - 9
                && YEAR ( [Date] ) <= YEAR ( selected )
        )
    )

Regards,

Xiaoxin Sheng