Defaulting date range with date slicer

[racope]

Is there a way in DAX to specify the start and ending date used in a date range slicer? I need the default start date to be the first day of the month from 2 years ago and the default end date to be the last day of the previous month. So when opening the report today the start date should be 11/1/2019 and the ending date should be 10/31/2021. Both dates should automatically update to the next month as a new month starts.  Is this possible?  

[v-cazheng-msft · Accepted solution]

Hi @racope 

You may try this Measure.

IsOrNot =

VAR Today_ =

    TODAY ()

VAR Year_ =

    YEAR ( Today_ )

VAR Month_ =

    MONTH ( Today_ )

VAR StartDate =

    DATE ( Year_ - 2, Month_, 1 )

VAR EndDate =

    DATE ( Year_, Month_, 1 ) - 1

RETURN

    COUNTX (

        FILTER (

            'calendar',

            'calendar'[Date] >= StartDate

                && 'calendar'[Date] <= EndDate

        ),

        'calendar'[Date]

    )

And then, use this Measure to filter your date Slicer.

Then, the result should look like this:

For more details, please refer to the attached pbix file.

Best Regards,

Community Support Team _ Caiyun

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. If you still have problems on it or I misunderstand your needs, please feel free to let us know. Thanks a lot!

[rbriga]

I've tested this solution, but it misses out on the desired effect.

1. Create a measure that will help us filter the days:

Available Dates =
VAR _LastDate =
    DATE ( YEAR ( TODAY () ), MONTH ( TODAY () ), 1 ) - 1
VAR _FirstDate =
    DATE ( YEAR ( TODAY () ) - 2, 1, 1 )
RETURN
    COUNTROWS (
        FILTER (
            'Calendar',
            'Calendar'[Date] >= _FirstDate
                && 'Calendar'[Date] <= _LastDate
        )
    )

2. Select the date slicer and open the filter pane.

Under "Filters on this visual", drag our new Available Dates measure.

Select "is not blank", then apply. I suggest that you hide and lock this filter.

This limits the slicer the way you wanted, but until you move the sliders\ values from their start\end (default) positions, PowerBI assumes you haven't filtered anything, so it shows dates outside that range until you move the slider.

However, the answer may lie in the date table itself.

1. Use the query editor to add a column which flags (Yes\ No) the dates in the range you've defined. Let's call it Available Date.

2. In the filter pane, add a page\report-wide filter: "Available Date is Yes".

[amitchandak]

@racope , no there is no option. You can log an idea for that https://ideas.powerbi.com/ideas/

There is an work around

Default Date Today/ This Month / This Year: https://www.youtube.com/watch?v=hfn05preQYA