Implementing a date slicer that let's you select single years or last 12 months

[PeterSimonsen87]

Hi Power BI community!

I'm trying to implement a slicer with the options to choose between seeing data from the last 12 months or individual years.

(something like this)

And I keep running my head into a wall. I've been thinking of solutions using field parameters, disconnected table, grouping and more. I think what makes it tricky is the fact that the selections are overlapping. Any ideas on how to achieve this will be rewarded with internet points and good vibes.

I made an mwe, but as I feel pretty stuck on square on, it might not make a lot of sense.

[v-eqin-msft · Accepted solution]

Hi @PeterSimonsen87 ,

Let's assume there is a Date column in your table, you will need to add a new table for slicer with all distinct Years and another value "Last 12 mths", please try this:

For Slicer = 
var _year= VALUES('Table'[Date].[Year])
return UNION(ROW("date_selection","Last 12 mths"),_year)

Then create a flag measure

Flag = 
var _diff=DATEDIFF(MAX('Table'[Date]),TODAY(),MONTH)
return IF(MAX('For Slicer'[date_selection])="Last 12 mths", IF(_diff>=0 && _diff<=12,1,0),IF(YEAR(MAX('Table'[Date]))=CONVERT(MAX('For Slicer'[date_selection]),INTEGER),1,0))

And apply it to visual-level filter pane, set as "is 1":

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

[PeterSimonsen87]

Thanks a lot, @v-eqin-msft ‌🙏‌ Works like a charm 🙂

(I was on vacation, so sorry about the slow reply)

[AndrewDavies437]

Hi, the easiest option would be to have a seperate filter (click the dropdown in top right of slicer) and change it to this setting. If this is too messy/not what you want, i'd recommend making a copy of your data which is filtered to only show the last 12 months of data and copy your report into another page that uses only this table. You could use bookmarks to swap between last 12 months or all data. 

These are just some suggestions. 

The longer way to do it would be to create a column in power query which returns true if the data is within the last 12 months then filter based on that column. I don't think you'll be able to have all those filter options in one slicer, but someone here with more knowledge may be able to do that for you. 

[PeterSimonsen87]

Thank you for the answer, Andrew! 🙂

I was hoping that there was a way to do it with one slicer (if using slicers at all).

And I think you are on to something with the bookmarks. Maybe it could work with setting up buttons that have a design similar to slicers and get a slicer-like functionality using bookmarks?

[AndrewDavies437]

Bookmarks are always a savior when you need weird functionality like this! Give it a go. Good luck my friend! 🙂