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

[Anonymous]

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

[BhaveshPatel]

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.

[Anonymous]

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

 

[parry2k]

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. 

[Anonymous]

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!

[parry2k]

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?

[Anonymous]

Hello @parry2k I still don't get it.

can you please elaborate?

ty,

[parry2k]

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

[Anonymous]

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.

[parry2k]

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

[Anonymous]

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

[Anonymous]

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.

[parry2k]

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.

[Anonymous]

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.

[parry2k]

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?

[Anonymous]

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

[v-ljerr-msft · 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).

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.

Regards

[Anonymous]

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

thanks, I will do just that.

best,