Forum Discussion
Create Dynamic Date range Slicer that changes to new date based on data
Hi Anonymous 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.
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.
Hi Anonymous
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:
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.
Hi Anonymous
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!
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?