Update Date slicer, but still selectable "between" function.

[bfeely]

I'm trying to find a way to format a date slicer in my powerbi report.

I want the slicer to provide the user with the ability to show data between 2 selected dates, so the user will select a start date and end date from the slicer.

However, I also want the slicers default dates to update daily as the model is refreshed with new data. So I want the end date default to be TODAY()-1 and the start date default to be the 1st day of the previous month.

So far, I've only been able to achieve one or the other.  I've tried creating an extra column with a DAX function to return "END" for today-1, and the date of all values previous.  However, I lose the date formating doing this and end up with text string, which means I loose the ability to filter for falues "between".

Any help?  Cheers folks

[DataNinja777 · Accepted solution]

Hi @bfeely ,

To format a date slicer in Power BI while keeping the "Between" functionality and dynamically updating the default date range, you need to pre-filter the slicer values. Since Power BI does not allow setting dynamic default values in slicers directly, a workaround is to apply a visual-level filter on the date field.

First, create two measures to define the default start and end dates. The start date should be the first day of the previous month, which can be calculated with:

Default_Start_Date = DATE(YEAR(TODAY()), MONTH(TODAY())-1, 1)

The end date should be yesterday’s date, which is:

Default_End_Date = TODAY() - 1

Once these measures are created, apply them as filters to the date slicer. Select the date field in the slicer, go to the Filters Pane, and set an advanced filter where the date is "on or after" [Default_Start_Date] and "on or before" [Default_End_Date]. This ensures that when the model refreshes, the slicer will only show values within this dynamic range by default. However, users can still manually adjust the dates as needed.

If you need a reference column to label these default dates, you can create a calculated column:

Date_Label = 
IF('Calendar'[Date] = TODAY() - 1, "END",
   IF('Calendar'[Date] = DATE(YEAR(TODAY()), MONTH(TODAY())-1, 1), "START", BLANK()))

This column can be displayed in a table visual for verification, but it should not be used directly in the slicer since it converts dates to text, breaking the "Between" functionality. This method ensures that your slicer always updates dynamically while maintaining flexibility for users to modify the selected date range.

Best regards,

[johnt75 · Accepted solution]

You're on the right lines with the new column. Create a column in your date table like

Date for grouping =
SWITCH (
    TRUE (),
    'Date'[Date]
        = TODAY () - 1, "End",
    'Date'[Date]
        = EOMONTH ( TODAY (), -2 ) + 1, "Start",
    'Date'[Date]
)

Create a duplicate of the 'Date'[Date] column as e.g. 'Date'[Date for range slicer]. Use this duplicate column in your slicer.

Open Tabular Editor and select 'Date'[Date for range slicer]. Change the Group By property and set it to 'Date'[Date for grouping]. Save your changes back to Power BI desktop.

Now, whenever you use 'Date'[Date for range slicer] on a slicer Power BI will store the values from 'Date'[Date for grouping], so if you publish the report with the correct dates selected that will get updated to reflect the new "correct" values each time you load the report.

[DataNinja777 · Accepted solution]

Hi @bfeely ,

To format a date slicer in Power BI while keeping the "Between" functionality and dynamically updating the default date range, you need to pre-filter the slicer values. Since Power BI does not allow setting dynamic default values in slicers directly, a workaround is to apply a visual-level filter on the date field.

First, create two measures to define the default start and end dates. The start date should be the first day of the previous month, which can be calculated with:

Default_Start_Date = DATE(YEAR(TODAY()), MONTH(TODAY())-1, 1)

The end date should be yesterday’s date, which is:

Default_End_Date = TODAY() - 1

Once these measures are created, apply them as filters to the date slicer. Select the date field in the slicer, go to the Filters Pane, and set an advanced filter where the date is "on or after" [Default_Start_Date] and "on or before" [Default_End_Date]. This ensures that when the model refreshes, the slicer will only show values within this dynamic range by default. However, users can still manually adjust the dates as needed.

If you need a reference column to label these default dates, you can create a calculated column:

Date_Label = 
IF('Calendar'[Date] = TODAY() - 1, "END",
   IF('Calendar'[Date] = DATE(YEAR(TODAY()), MONTH(TODAY())-1, 1), "START", BLANK()))

This column can be displayed in a table visual for verification, but it should not be used directly in the slicer since it converts dates to text, breaking the "Between" functionality. This method ensures that your slicer always updates dynamically while maintaining flexibility for users to modify the selected date range.

Best regards,