PowerBI fixing custom start date for Date Range slicer

PowerBI fixing custom start date for Date Range slicer

Problem Description:

In PowerBI while using DateRange slicer is it possible to fix the Start Date as 01-06-2022 and End Date as today’s date and still we can select older dates from the Date Range selector so that we can list records available for older dates.

I did try to put static start date for date range but that disables the older dates for which there are records available.

Solution – 1

You can do it in a measure like this if you prefer:

Creating a date table in your model is considered as one of the best BI practices. It helps sorting, filtering, and grouping in your analytics calculations!

Your_Measure =
    FILTER (
        ALL ( Calendar[Date] ),
        Calendar[Date] >= DATE ( 2022, 06, 01 )
            && Calendar[Date] <= TODAY ()

Update requested from @Mano

Here is the Calendar table you need to create! Do not put today() function here! This is a dimensional date table in a star-schema data model. When you write your measure, You need to integrate it there. I will write it for you now!

Calendar =
VAR _cal =
        DATE ( YEAR ( MIN ( email_notification_info[email_notification_sent] ) ), 01, 01 ),
        DATE ( YEAR ( MAX ( email_notification_info[email_notification_sent] ) ), 12, 31 )
VAR _result =
        "Year", YEAR([DateColumn]),
        "MonthNumber", MONTH([DateColumn]),
        "MonthName", FORMAT ( [DateColumn], "mmmm" ),
        "Period", FORMAT ( [DateColumn], "YYYY-MM" )

Now Your final measure is almost the same as above:

    Your_Measure =
VAR your_variable =
    DATE ( 2022, 06, 01 ) -- Enter any beginning date here!
VAR Result =
        [your_measure here],
        FILTER (
            ALL ( Calendar[DateColumn] ),
            Calendar[DateColumn] >= your_variable
                && Calendar[DateColumn] <= TODAY ()
Rate this post
We use cookies in order to give you the best possible experience on our website. By continuing to use this site, you agree to our use of cookies.