Create Dynamic Date range Slicer that changes to new date based on data

[samsunakpan]

I have a  Date range Slicer that works with the date field in the DateTable. The date on the slicer does not update to reflect new data after the report has been published. Every time we refresh the report, I want to see the dates in the slicer updated according to dataset not defaulting to the date it had as at when the report was published.

I have checked all the forums to see which method is working but I only come across articles pointing to steps that does not produce the result I am looking for.

This was the last known method I used Set a Dynamic/Default/Changing Date in Power BI Date Slicer - YouTube  Rush published this video about a year ago. The steps does not work any more on the current power bi report.

[Anonymous · Accepted solution]

Hi @samsunakpan The steps I followed and which worked for me is, I readded a Academic Year slicer and Date Slicer in report. According to the requirement, I wanted to remove interaction on 'Academic Year' slicer and something unusual is, the date slicer lost it's interaction with all other visuals automatically. I retested it again and it is same. So, basically the fix is to remove the blocked interaction on Date Slicer. I am not sure if this problem is with recent version of PBI.

[samsunakpan]

Thank you so much for coming back to me with the solution. I checked to see that PowerBI by default disabled the interaction from the date slicer to the Year slicer. I just did not see that. Enabling the interaction solved the issue. 
Thank you.

[Anonymous]

Hi all, 
I am looking for a similar solution where the Date range (with between) changes or picks up the max date with selection from Academic year slicer. As in my semantic model, for Academic Year 2021/22 ,max date is 28/08/24 and similarly for 2022/23 , max date is 27/08/24 and so on.

 Any help is appreciated!

[SamWiseOwl]

Hi @samsunakpan 

I assume you are meaning a single select slicer that isn't updating? This is an issue I have encountered before.
My work around is to use the Filter pane to apply a Top 30 filter based on the date itself.

Here for example it always shows the last 30 dates worth of data:

I have tested it on a double select slider and it appeared to work also.

If this helps please mark it as a solution for others to find 🙂

[samsunakpan]

@SamWiseOwl Thank you for your response. This is how the slicer looks like

The Date range should update to only the days transaction happened in the year. That is the minimum date will show the first date while the maximum date will update to show the last date that is available in the data. For the current year the maximum date will continously change as new data becomes available even after report has been published. 
Unfortunately the Filter option does not fit this solution. Thank you so much for your contribution. 

[SamWiseOwl]

Hi @samsunakpan 

Apologise I must be misunderstanding, this is what I have done.
Set the Year dropdown slicer to Top 1 by Max Calendar Year this will change whenever the calendar table is refreshed by new data.

Under Format make sure the drop down slicer can FILTER the Date slicer using interactions.

Here I have added a new date into my model which regenerates the Calendar table which causes the dropdown to change updating the Date slicer:

[samsunakpan]

@SamWiseOwl Thank you. This implementation does not fit this solution. Imagine 2018 is selected in the Year, I want the Date slicer to automatically switch to show date that applies to 2018 data and if new data  is added for 2018 the Date range should adjust to accomodate the new date in the data. I have implemented the relationship between the date table and the fact table in the model. I have also edited the interaction so that it is only the Year slicer that interacts with Data slicer. All of this has already been handled. The solution you shared does not work for this. See how the date slicer should work here https://www.youtube.com/watch?v=D4AF9lQCick

Thank you so much, I really appreciate your suppport.

[SamWiseOwl]

Hi @samsunakpan 

Daft question but if the refresh working in the browser, are you getting the data pulled through? 
When testing this in the desktop when I call a refresh it does appear to be working.

If it is pulling through the data correctly the next thing I would check is that you have Persistent filters turned off:

This saves the last filters applied to a report. 

If it is neither of these let me know!

[samsunakpan]

Thank you for the response. In the the browser, each refresh brings in new data. If the report was published on 1st August, 2024 the report will carry this date as default even when there will be new data for 2nd August and no filters applied. The max date in the date range will still be showing 1st August even if you clear filters or turn off saving filters. Until you change the maximum date manually on the report before you can see data for 2nd August. If you publish same report again it continues to retain the date without automatically adjusting to show the new date.

I want the Year slicer to still remain a dropdown and show past years but anytime a year is switched, the date should automatically change to show the dates corresponding the the date available for the selected year in the fact table. This used to happen automatically when you publish the report without clicking on the date slicer to default to a particular date.

Does this provide any insight to the issue?

[samsunakpan]

@raji_n Thank you for your response. What I am looking to get is slightly different. I am using a Date range that has a minimum and maximum (between). both values are suppose to update based on the available dates in the data not necessarily pointing to today if there is no record for today. 

[raji_n]

I would suggest you to have a today as slicer option, so that whenever it is refreshed, it points to the most recent date.

https://www.youtube.com/watch?v=pt7dI8af_m8

@samsunakpan