Making Chart Filter Dynamic

[WishAskedSooner]

Hi Experts!

I am getting quite close to a complete solution to my problem. I am playing around in the Adventure Works PBIX. I created a YTD vs PY YTD line chart for Sales[Sales Amount]. To do this properly, I created an unconnected Dates table, 'DateUnconn', and added a column: [Year] = Year('DateUnconn'[Date]).

Then, I created a Line Chart using [Month] from 'DateUnconn' as the X-axis and two measures I created for Sales_YTD and Sales_PY_YTD. Everything works as expected.

Now, I only want to plot the current year versus prior year, not a time series across all years. Therefore, I add a [Year] filter to the Line Chart and set it to 2020.

My only remaining concern, is how do I make this filter dynamic? Because right now, I will have to update the filter manually every year.

Thanks so much for the help!

[Anonymous · Accepted solution]

Hi @WishAskedSooner ,

Thanks to lbendlin , Kedar_Pande  and dk_dk  for their interest in this issue. I have some other thoughts to add:

Based on your description, you want the filter to be dynamic, right?
Here is my test data:

We can create a measure.

Flag = 
var _slicer=SELECTEDVALUE('Sales'[Category])
var _max_date=CALCULATE(MAX('Sales'[Date]),FILTER(ALLSELECTED('Sales'),'Sales'[Category]=_slicer))
var _max_year=YEAR(_max_date)
RETURN IF(ISFILTERED('Sales'[Category])=FALSE() || YEAR(MAX('Date'[Date]))=_max_year,1,0)

Place [Flag=1] on the visual object's filter. Then the result is as follows.

Best Regards,

Neeko Tang

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

[Kedar_Pande]

Create a Measure

IsCurrentOrPreviousYear = 
IF(
'DateUnconn'[Year] = YEAR(TODAY()) || 'DateUnconn'[Year] = YEAR(TODAY()) - 1, 
1, 
0
)

In your line chart, add this measure (IsCurrentOrPreviousYear) as a filter and set the filter to 1. This will dynamically show only the current year and previous year data.

If this helped, a Kudos 👍 or Solution mark would be great!
Cheers,
Kedar Pande
www.linkedin.com/in/kedar-pande

[dk_dk]

Hi @WishAskedSooner (great username! 😄 )

You could do this by creating a calculated column:

InCurrYear = YEAR('DateUnconn'[Date]) = YEAR(TODAY())

This will evaluate to True for all date values that are in the current year, and False for all others. You can then add this column as a visual or page level filter.

Hope this helps!

[lbendlin]

Because right now, I will have to update the filter manually every year.

What's wrong with that?  Usually companies keep the reports for completed years up a little longer before switching to the blank sheet of the new year.  Better to control that manually.

[WishAskedSooner]

Hi @lbendlin! Thank you for you reply. Perhaps I should've provided a bit more context.

Let's say your dealing with data that doesn't align. For example, Bikes data is on a one month lag, Clothing is on a two months lag, Accessories is on a three months lag, and so on. The user always wants to see the most up to date YTD.

Bikes have data through January 2021. The user clicks Bikes in the slicer => update the Year filter automatically to 2021.

Accessories have data through November 2020. The user clicks Accessories in the slicer => update the Year filter automatically to 2020.

This may sound like a strange requirement, but for better or worse, that is what our organization deals with.

Is this possible?

[lbendlin]

Please provide sample data that fully covers your issue.
Please show the expected outcome based on the sample data you provided.