Forum Discussion

WishAskedSooner's avatar
WishAskedSooner
Continued 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 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!

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. 

6 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    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
    Super 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
      Continued 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
        Super 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.

  • dk_dk's avatar
    dk_dk
    Super User
    1 year ago

    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!

  • Kedar_Pande's avatar
    Kedar_Pande
    Super User
    1 year ago

    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