Reply
Highlighted
Frequent Visitor
Posts: 5
Registered: ‎04-20-2017
Accepted Solution

Filter on year BEFORE selected year in slicer

Hi all,

 

I have a slicer in my report for Year. Most of my visuals need to be filtered on that year, which of course works just fine. However, I have one chart for which I want to show a sum per year for all years BEFORE the selected year. In other words, if the user selects 2017 in the slicer, my chart should show data for 2014, 2015, 2016 (or whichever years I have data for, but only up until 2016). Is this possible? 

 

Thanks in advance!


Accepted Solutions
New Contributor
Posts: 499
Registered: ‎09-19-2016

Re: Filter on year BEFORE selected year in slicer

HI @betski,

 

Has been testing several scenarios but do not now how your data is fully set up. But check if this post can help you.

 

https://community.powerbi.com/t5/Desktop/Slicer-reverse-selection-in-chart/td-p/96316

 

Regards,

 

MFelix

View solution in original post


All Replies
New Contributor
Posts: 499
Registered: ‎09-19-2016

Re: Filter on year BEFORE selected year in slicer

Hi @betski,

 

If you select the slicer then on the top bar select the modeling and then the Edit Interactions all of the visuals will show three options: filter, highlight, do nothing. chose the do nothing (forbidden sign) and filter your visual direcly. The image below shows an example.. Th

 

graphs.png

 

Regards,

MFelix

Frequent Visitor
Posts: 5
Registered: ‎04-20-2017

Re: Filter on year BEFORE selected year in slicer

Hi @MFelix and thanks for your reply! Not sure how I would get the filter on the chart to be correct, though... I don't want to hard code it, it should depend on the value in the Year slicer. If the user selects 2016 in the slicer, the chart should show data up until 2015, if they select 2015, the chart should show data up until 2014, etc.

New Contributor
Posts: 499
Registered: ‎09-19-2016

Re: Filter on year BEFORE selected year in slicer

In that case you have to make a measure and used it in your visual.

 

Questions:

 

Do you use a measure or a column in your visual that summarizes?

Where does the year comes from? A date table?

 

Regards

MFelix

Frequent Visitor
Posts: 5
Registered: ‎04-20-2017

Re: Filter on year BEFORE selected year in slicer

The year comes from a time dimension in my tabular SSAS. The chart summarizes a measure (invoice amount) by year (in the time dimension). How do I make a measure to use for this - I will not know beforehand what year the user will select in the slicer?

New Contributor
Posts: 499
Registered: ‎09-19-2016

Re: Filter on year BEFORE selected year in slicer

Can you please show me how your measure invouce amount is calculated?

Frequent Visitor
Posts: 5
Registered: ‎04-20-2017

Re: Filter on year BEFORE selected year in slicer

There is no special calculation - it is a simple measure added directly from the fact table, summarized per year in a stacked column chart (Time on the axis and Invoice Amount as Value). All I want to do is to add a filter on year saying that the chart should contain only years earlier than the selected value in the Year slicer.

New Contributor
Posts: 499
Registered: ‎09-19-2016

Re: Filter on year BEFORE selected year in slicer

HI @betski,

 

Has been testing several scenarios but do not now how your data is fully set up. But check if this post can help you.

 

https://community.powerbi.com/t5/Desktop/Slicer-reverse-selection-in-chart/td-p/96316

 

Regards,

 

MFelix

Frequent Visitor
Posts: 5
Registered: ‎04-20-2017

Re: Filter on year BEFORE selected year in slicer

That might do the trick! Thanks a lot for posting the link.