Forum Discussion
Advocate IVSetting date slicer end date to today
Microsoft EmployeeHi cjemmott,
If I understand you correctly, you can use CALENDAR function (DAX) to create a new Calendar table to your mode like below.
New Date Table = CALENDAR ( MIN ( 'Your_Table_Name'[DATE] ), TODAY () )
And create a relationship between your original table and this new Calendar table with the Date column.
Then you should be able to use the Date column from this new Calendar table as Slicer on your report, whose end date will always be the current data(today) if your report refreshes daily or more frequently on Power BI service. :smileyhappy:
Regards
Advocate IVI guess I should have mentioned that I am using DirectQuery on Power BI Premium.
- v-ljerr-msft
Hi cjemmott,
After a few tests, I found a simpler way to set date slicer end date to today with DirectQuery in Power BI.
Instead of creating a new calendar table, we can just use the formula below to add a new calculate column to indicate if the date is less or equal than today, then use it as a visual/page/report level filter(Column is 1) on the report in this scenario. :smileyhappy:
Column = IF ( contents[Content Creation Date] <= TODAY (), 1, 0 )
Regards
- cjemmott
Thank you for helping! Unfortunately, I don't think that is doing what I want.
The problem I am trying to solve is that there have not been updates for several days, but I want the date slider to still end at today. For example, today is Aug 7, but the last update was Aug 1. I want the date slicer to show Jan 1 - Aug 7.
The filter you provided works well to eliminate future dates, but in the example I tried did not seem to change the slicer end date from Aug 1 to Aug 7.
- v-ljerr-msft
Hi cjemmott,
Yes, the solution could work only when there is a Calendar table that contains future dates in your data mode(you should be able to add this calendar table at source side, if possible). :smileyhappy:
Regards