Update Chart Filter Dynamically Based on Slicer Selection

[WishAskedSooner]

Hi Experts!

I am investigating data lags in the Adventure Works PBIX. Accordingly, I have made some updates to the Sales table by trimming the data for each category as follows:

Accessories: No data past November 2019

Bikes: No data past May 2020

Clothing: No data past June 2020

Components: No data past February 2020

Requirement:

The user wants to view a single year plot of Sales performance by Category with the most up-to-date YTD Sales Amount versus Prior Year YTD Sales Amount.

Partial Solution:

I have created the following two measures that work as expected:

Sales_YTD =

    VAR _LatestOrderDateKey = MAX('Sales'[OrderDateKey])

    RETURN

        CALCULATE(SUM('Sales'[Sales Amount]), DATESYTD(CALCULATETABLE(VALUES('Date'[Date]), 'Date'[DateKey] <= _LatestOrderDateKey)))

Sales_PY_YTD = CALCULATE(SUM('Sales'[Sales Amount]), SAMEPERIODLASTYEAR(DATESYTD('Date'[Date])))

These are added to a plot along with a slicer on Category. Lastly, I added a Calculated Column to the Date table:

[Year] = YEAR('Date'[Date])

and used this as a filter in the plot which I hard coded to 2020. See below.

Problem:

When the user clicks on Accessories, since the data is only through November 2019, the Year filter in the plot needs to be updated to 2019. Below is what the plot should update to if I hard code the filter value to 2019.

How do I make the filter value update automatically to 2019 when the user selects Accessories versus Bikes, etc?

Thanks in advance for the help!

[dharmendars007]

Hello @WishAskedSooner , 

You can use the below measure to get the MAX year and the use this measure to in a measure Dynamic Year and add this in the visual filter

MaxYearByCategory =
CALCULATE(MAX(Sales[OrderDateKey]),
Sales[Category] = SELECTEDVALUE(Sales[Category]))

DynamicYear = YEAR([MaxYearByCategory])

If you find this helpful , please mark it as solution which will be helpful for others and Your Kudos/Likes 👍 are much appreciated!

Thank You

Dharmendar S

LinkedIN 

[WishAskedSooner · Accepted solution]

@dharmendars007 Thank you so much for your reply!

I tried your solution with some slight modifications:

I created the following three measures:

LatestOrderDateKey = MAX(Sales[OrderDateKey])

LatestOrderDateYear = MAXX(FILTER('Date', [DateKey] = [LatestOrderDateKey]), [Year])

Filter_YTD = IF(VALUES('Date'[Year]) = [LatestOrderDateYear], 1, 0)

So far, the first two measures work perfectly since they are filter-context sensitive. The LatestOrderDateYear updates to 2019 versus 2020 properly when put in a card. See below.

Next, I replaced the Year filter with the Filter_YTD measure in the Plot filters and set the value to is 1. However, the plot is not working as expected.

I feel like I am so close and just need a little nudge in the right direction. Thank you so much for your help!