cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
cjemmott Regular Visitor
Regular Visitor

Setting date slicer end date to today

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?

5 REPLIES 5
Highlighted
Microsoft v-ljerr-msft
Microsoft

Re: Setting date slicer end date to today

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. Smiley Happy

 

Regards

cjemmott Regular Visitor
Regular Visitor

Re: Setting date slicer end date to today

I guess I should have mentioned that I am using DirectQuery on Power BI Premium.

 

3.PNG

Microsoft v-ljerr-msft
Microsoft

Re: Setting date slicer end date to today

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. Smiley Happy 

Column = IF ( contents[Content Creation Date] <= TODAY (), 1, 0 )

 

Regards

cjemmott Regular Visitor
Regular Visitor

Re: Setting date slicer end date to today

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.

Microsoft v-ljerr-msft
Microsoft

Re: Setting date slicer end date to today

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

 

Regards

Helpful resources

Announcements
New Kudos Received Badges Coming

New Kudos Received Badges Coming

Kudos to you if you earned one of these! Check your inbox for a notification.

Microsoft Implementation for Communities Wins Award

Microsoft Implementation for Communities Wins Award

Learn about the award-winning innovation that was implemented across Microsoft’s Business Applications Communities.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Kudoed Authors (Last 30 Days)
Users online (3,506)