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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.