Current year and previous year while the slicer is set to filter only the current year?

[Cwaniaczek]

How can I show a bar chart for the current year and previous years while the slicer is set to filter only the current year?
How can I create a bar chart in Power BI that displays values for the current year and the previous year, when the slicer is set to filter only the current year?

- I created a bar chart and added years to it, and I also created a slicer for the years. I want the chart to display data starting from the selected year (for example, 2022) and include data for 2 previous years and  as well.
After selecting any year on the slicer, I want to see that year as well as the two previous years on the chart.

- I also have data for future years, such as 2023 and 2024, but I don't want to display them initially. I want to see only the selected year, for example, 2024, and the previous year, 2023, after selecting 2024 on the slicer.

Thank you in advance for your help! I hope my question is clear, but please feel free to ask if you need any clarification.

[Anonymous · Accepted solution]

Thank you FreemanZ

Hi, @Cwaniaczek 

Based on your description, I have the following dataset:
A table of dates and a table of facts:

First, I use the following expression to create a table with a year slicer:

YearSlicerTable = VALUES(DateTable[Year])

Then I created a measure using the following expression:

LastYear = 
VAR _currentSeleted_year = SELECTEDVALUE('YearSlicerTable'[Year])
RETURN IF(SELECTEDVALUE(DateTable[Date].[Year])<=_currentSeleted_year && SELECTEDVALUE(DateTable[Date].[Year])>=_currentSeleted_year-1,1,0)

Use this metric in the Filter panel and set it to 1:

Create a slicer using the fields of the Year slicer table:

Here are the results:

How to Get Your Question Answered Quickly

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards

Jianpeng Li

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

[Cwaniaczek]

Hi @Anonymous once again.

I have an additional question, unfortunately the proposed slicer does not work with the rest of the visualization and report. To be more precise.

I have one main slicer that I use on all the report pages (they are synchronized) on the page with the chart we are discussing there are also other visualizations.

On the left I applied your solution (marked with a red star)

on the right I have a different visualization based on Total measure:

Total Sales Amount = SUM(Sales[Amount]) and filtered by categories.

I would like this pie chart to show only 2023. At the same time, the chart on the left (marked in red) should show the current year, i.e., for example, 2023 and 2022 - at which point only one year is marked on the slier.

As you can see in the picture the main slicer does not work with yours, also it does not help to remove interactions. 

Is it possible to customize this somehow?
Many thx in advance! 

[Cwaniaczek]

@FreemanZ 
@Anonymous 

Wow, thanks to both of you for the solution!
I hope it will work in my case as well. Below I paste what I was able to do following your advice!

[FreemanZ]

hi @Cwaniaczek ,

try to feed the slicer with additional unrelated table and reference the selected year with SELECTEDVALUE in your original measure.