Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
PeterSimonsen87
Frequent Visitor

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

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.

 

Screenshot 2022-06-15 113544.png

 

 

 

 

 

 

 

(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.

 

1 ACCEPTED SOLUTION
v-eqin-msft
Community Support
Community Support

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)

Eyelyn9_0-1655781270543.png

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":

Eyelyn9_1-1655781328176.png

Eyelyn9_2-1655781338257.pngEyelyn9_3-1655781349221.png

 

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.

View solution in original post

5 REPLIES 5
v-eqin-msft
Community Support
Community Support

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)

Eyelyn9_0-1655781270543.png

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":

Eyelyn9_1-1655781328176.png

Eyelyn9_2-1655781338257.pngEyelyn9_3-1655781349221.png

 

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.

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


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

 

AndrewDavies437_0-1655286588032.png

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. 

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?

 

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

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors