Setting date slicer end date to today

[cjemmott]

My dashboard includes a slicer based on a date.  Sometimes there are no new entries for a few days, so the date slicer will show an end date that is not today.  This has been causing confusion with customers - they think the dashboard isn't current.

Is there a way to specify the end date for a date slicer?  Or a different way to force the end date to be today?

[v-ljerr-msft]

Hi @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. 

Regards

[cjemmott]

I 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.  

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

Regards