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
Anonymous
Not applicable

Limit Time slicer values to the range that is in the dataset

Hello,

 

I have a timeslicer in a report with values from 1/1/2015 to today. Is there a way to limit the options that the user has to set the time frame to the values that are present in the dataset?

 

thanks

1 ACCEPTED SOLUTION

Hi @Anonymous,

 

When we click the Current Selected Year(let's say 2015) to set the Year for a Time Slicer, the list of available years for selection will be from the Current Selected Year-10 to Current Selected Year+10(from 2005 to 2025 for the example year 2015).

 

s1.PNGs2.PNG

 

This is the default behavior for Time Slicer, and there is no option to change it currently. In this scenario, I would suggest you to submit your requirements on Power BI Ideas and vote it up to improve this feature.Smiley Happy

 

Regards

View solution in original post

17 REPLIES 17
BhaveshPatel
Community Champion
Community Champion

Yes, You can limit the options by dragging the date column to Page level filters. You can manually select the options required for the dates to be filtered and timeline slicer will automatically adjust the time period selected as per the page level filter.

Thanks & Regards,
Bhavesh

Love the Self Service BI.
Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to give Kudos.
Anonymous
Not applicable

Hello @BhaveshPatel, thanks for the reply.

 

I haven't thought about putting the date collumn in the page filters but even so I get options like the screenshot.

What I would like to have is to limit the options to after 1/1/2015 and not after today().

 

 

I believe you have continuous calendar table in your data model, if yes then you can add a calculated column to get DaysPassed in calendar using datediff function and then in filter add, DaysToday is less than equal to 0.

 

Let me know if you need further help on how to achieve this and I can send you the screen shots. 



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Anonymous
Not applicable

Hi @parry2k, thanks for the reply,

 

I think I lost you at the DaysToday, how do I define this measure? It's not clear to me how I will going to use the DaysPassed also.

 

thanks a lot!

 

I think it was typo, I was going to say DaysToday is less than equal to 0, let me know if you need help on how to do it?



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Anonymous
Not applicable

Hello @parry2k I still don't get it.

can you please elaborate?

 

ty,

 

Sure, can you share your data model  and their relation. Do you have continuous calendar table?



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Anonymous
Not applicable

hello @parry2k, unfortunately, I can not share the dataset, this is a direct query to an  azure SQL database but yes there is a continuous calendar table from 1/1/2015 to today, updating each day.

No worries, I will create a sample pbix and send it to you. Most likely tomorrow,



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Anonymous
Not applicable

thanks a lot @parry2k, I have been struggling with this for a week.

Anonymous
Not applicable

hello @parry2k, I have tried many different ways and none of them seems to be working, I am really curious on how you managed to solve this issue.

 

Ok, let's do this:

 

add a column in calendar table called DaysPassed

DaysPassed = DATEDIFF(Calendar[Date], TODAY(), DAY)

Put DaysPassed column to Page Level Filter with DaysPassed >= 0

 

Let me know if this works.



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Anonymous
Not applicable

screenshot.JPG

 

I have put the Dayspassed column to the table where i get the date and put also in the page level filters with advance filtering "great than or equal to" 0. still have the same options in the timeline slicer I can choose from 2005 to 2027. the database has dates between 1/1/2015 and today.

Can you create a table with following column and share the screen shot:

 

Date, DaysPassed

 

We want to make sure that DaysPassed calculation is correct and for today's date is Zero?

 

Also in slicer, are you using Date from Calendar table?



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Anonymous
Not applicable

yes, the date column is the one I've been using to create the slicer.

 

1.JPG2.JPG

Hi @Anonymous,

 

When we click the Current Selected Year(let's say 2015) to set the Year for a Time Slicer, the list of available years for selection will be from the Current Selected Year-10 to Current Selected Year+10(from 2005 to 2025 for the example year 2015).

 

s1.PNGs2.PNG

 

This is the default behavior for Time Slicer, and there is no option to change it currently. In this scenario, I would suggest you to submit your requirements on Power BI Ideas and vote it up to improve this feature.Smiley Happy

 

Regards

Anonymous
Not applicable

hello @v-ljerr-msft, not what I wanted to hear, although I was begining to suspect it.

 

thanks, I will do just that.

 

best,

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.