Forum Discussion

WishAskedSooner's avatar
WishAskedSooner
Icon for Continued Contributor rankContinued Contributor
1 year ago
Solved

Making Chart Filter Dynamic

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 thi...
Need Help
  • Anonymous's avatar
    Anonymous
    1 year ago

    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. 

lbendlin's avatar
lbendlin
Icon for Super User rankSuper User
1 year ago
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's avatar
WishAskedSooner
Icon for Continued Contributor rankContinued Contributor
1 year ago

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's avatar
    lbendlin
    Icon for Super User rankSuper User
    1 year ago

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