Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
cjemmott
Advocate I
Advocate I

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
v-ljerr-msft
Employee
Employee

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

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

 

3.PNG

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

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.

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.