Problem with time slicer (not updating as time goes by)

[Trish_Rye]

Hello,

My situation is as follows:

1) I limit the data I am working with in Desktop to first 1000 rows as the dataset is too large (I use parameters to achieve this and I change the value of the parameter after I publish on Power BI Service, before the first refresh).

2) I am using a time slicer with relative data and a slider for the user to set the period s/he needs data for:

3) The report shows data for the last 3 months.

As a result what I see in Desktop is not what I will see in Power BI Service, also in terms of max / min date. Plus the max date should change every day as the data is updated. Naturally, the date slicer is permanently set on the max date I had in my Desktop version when publishing. To see the current max date, the users must slide slider themselves (as you can see above).

The problem is that:

1) the client doesn't like it (they'd rather the date slider updated automatically with no action required from the user)
2) the client is afraid that the date slicer will break when we reach / go past the date on which the report was published (and which is still shown as max date if the user doesn't do anything)

Is there any solution to this problem.


Thanks in advance!

[Anonymous]

Your ideas is so great Tutu_in_YYC 

Hi, @Trish_Rye 

At this point, I think you might consider adding an extra slicer to dynamically display the previous 3 months' data. The slider slicer is reserved for the user to freely slide to display the corresponding date interval data.
In the past three months, there have been too many tutorials on the Internet about the dynamic display of slicers, and here we take SQL BI as an example:

Show last 6 months based on user single slicer selection - SQLBI

Show previous 6 months of data from single slicer selection - SQLBI

Educate your users to the extent that Power BI is able to do it as an effective solution.

Best Regards

Jianpeng Li

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

[Trish_Rye]

Thank you for an interesting solution. I will definitely look at the articles.

[Trish_Rye]

Hi,

Yes I know about this kind of date slicer (last X years / months, days, etc) and I suggested it to the client. However, they want to be able to set exact dates and did not agree to my proposal. So the question remains:

1) is this the only solution?

2) can my slider "break" after the time goes past the publishing date which is now visible as last date (and requires an interaction from the user to slide it to the actual, more current max date)?

[Tutu_in_YYC]

---

@Trish_Rye wrote:

1) is this the only solution?

2) can my slider "break" after the time goes past the publishing date which is now visible as last date (and requires an interaction from the user to slide it to the actual, more current max date)?

---

1) yes... the slicer is always going to set based on what you save before publish. And this applies to all slicers in power bi, not just date. To confirm this, i tested a few scenarios, waited a day before refreshing. And yes, the slicer will not update the max date even though new day has been added in after a refresh, which means your visuals will not update. 

2) it will not break. The user can still slide forward to the days past the publication date. 

[Trish_Rye]

Ok, thanks 🙂

However, I'm afraid, my phrasing of the second issue was not clear:

I meant that I'm afraid it might break in the following case:
As you can see in my screenshot, the report was published on 22nd October so the min date would be 3 monts earlier counting from this date. What will happen when 23rd October becomes my new min date (start date)?

[Tutu_in_YYC]

I see. K, it is going to depend on how you set up the date column/table. The slicer would just visualize the data that contains in the column that has the date.

 Are you using date column directly from the fact table? ie you will only get next date, when the next day transactions are posted in the table

And are you deleting any rows from the data model? eg, any transaction outside of the last 3 months will be deleted from the data model. This could be for example a filter in power query

[Trish_Rye]

I am using date column from the fact table (not a separate Calendar table), data type is date/time. Do you think situation could change when I start using a separate Calendar table? This would think interfere with cutting the last three months plus such setup would not take into consideration that there is a 2-day delay in data (refresh is daily but e.g. on 16-Dec we have data for up to 14-Dec).

The problem with the slicer occurs both:
a) when I filter out the last 94 days using Power Query:
Table.SelectRows(#"Changed Type", each Date.IsInPreviousNDays([Date], 94))
b) when I don't do it

[Tutu_in_YYC]

Having a separate date table will not solve our challenge here, but it is a best practice as power bi engine works best with dimensional model (star schema). I asked so that i have a better picture of how your model looks like. Thanks for explaining.

Unfortunately the "between" date slicer, will never update based on the date column you have even after semantic model refresh, because thats just the default behaviour of the "between" slicer mode. It will always default to what you have when you publish it.

I came across your business requirement challenge often. The users want to automate data context (in this case, default to the last 3 months automatically) but also flexibility to change the date, which depending on the data model either not possible or will require alot of DAX hackery. 

One thing that might be helpful is to create a dynamic card that let them know what is the range of the data ie Min(date) and Max(date)

This way they are aware of the date context they are looking at. Since you are filtering using power query, the duration will always in the past 94 days, and your visuals will always update to this 94 days data automatically when the report refreshes.

I have an idea, let me test something out. i will be back

[Tutu_in_YYC · Accepted solution]

Yo, so i tried to replicate your scenario multiple times. So i set up this slicer with relative date filter with last 5 days including today

using the data column from the table that will have the data i will be tracking (ie fact table), and also perform the same filter in power query to only bring in last 5 days data including today.

First publish on first day looks good. Then i waited the next day to update my data source, ie new data added for the day and old data older than 5 will be filtered out. Refreshed the report and refresh the browser, and looks like the date slicer updated too (ie min data and max date in the slicer increased by 1 day). Sounds like this could work for your scenario.

The only challenge i see would be if the browser cache the old information, so refreshing the browser or opening the report from scratch is probably a good idea. 

I highly recommend that you try setting it up like this, and check in everyday to see if you have the same behaviour as what i have. Im going to monitor mine for another few days to confirm it.

[Trish_Rye]

Thank you so much! I replicated the solution with 3 months of relative dates and I will monitor as well. I will probably check it in about a weeks' time as I am going on long Christmas holiday. Happy celebrations to you as well!

[Tutu_in_YYC]

It works for me, but I still require another relative filter in the Filter on this page to make sure that all the visuals are updating correctly too. Let us know how it goes. 

Happy holidays!

[Trish_Rye]

Hi,
Unfortunately, I haven't noticed your reply before holiday so my test was running refreshes without the Filter on page. Do you mean to set this up additionally (my report has more than one page)?

As far as the browser is concerned:
I guess either clearing the cache each time or using the incognito / private mode, should help here. I am using the latter so I think it should be enough inb this respect.

[Tutu_in_YYC]

I have both:
1. Filter on this visual (This is on the date slicer, so that it doesnt show any irrelevant dates)

2. Filter on this page/all pages (so the visuals are not showing irrelevant historical data)

Try this out. If you have multiple pages, you may need to re-do 1 on all the pages.

[Trish_Rye]

I looked through your tips again and checked that I have everything set up as you suggested:

1) relative date slicer for the last 94 days
2) the last 94 days filter on the page / all pages

3) the last 94 days cut using Power Query

4) clear browser cache (incognito mode)

I left the semantic model refreshing daily for a few days and I still have the same problem although I use incognito mode each time.

The minimum date is updating but the max date still needs to be dragged to the right by the user (today it should show 5-Jan-2025):

If I understand correctly, your experiment worked fine and the slicer is now showing 3 - 7 Jan 2025 (the last 5 days). Have you done anything more than I did as described above?

[Tutu_in_YYC]

1 more thing i could think of is Persistent Filter. Power Bi service remembers the last selection done by the user. Try disabling the feature.

https://powerbi.microsoft.com/en-us/blog/announcing-persistent-filters-in-the-service/

[Tutu_in_YYC]

What is the default date values you want it to automatically update to?
example:

• Always update the max value in the slicer to "today" or "yesterday"?
• Should the start date move too? everyday the start date move forward by 1 day?
• Also does the report get refreshed daily?

[Trish_Rye]

Thank you for being so dilligent!

1) Max date should be set to the day before yesterday (as there is a 2 days' long delay in the data). But I think we can set it to yesterday as well if it's a problem.
2) Yes, the start date should shift too if possible as we are after the last three months (but I believe it shifts already now as you can see in my screen from 10th December)

3) Yes, the refresh is daily

[Tutu_in_YYC]

Try using relative date slicer, if you know that you always want to look at the past X days/months etc

https://learn.microsoft.com/en-us/power-bi/visuals/desktop-slicer-filter-date-range