PowerBI Fixing Custom Start Date For Date Range Slicer

Contents hide

1 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 =
CALCULATE (
    [Total_Sales],
    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 =
    CALENDAR (
        DATE ( YEAR ( MIN ( email_notification_info[email_notification_sent] ) ), 01, 01 ),
        DATE ( YEAR ( MAX ( email_notification_info[email_notification_sent] ) ), 12, 31 )
    )
VAR _result =
    ADDCOLUMNS (
        _cal,
        "Year", YEAR([DateColumn]),
        "MonthNumber", MONTH([DateColumn]),
        "MonthName", FORMAT ( [DateColumn], "mmmm" ),
        "Period", FORMAT ( [DateColumn], "YYYY-MM" )
    )
RETURN
    _result

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 =
    CALCULATE (
        [your_measure here],
        FILTER (
            ALL ( Calendar[DateColumn] ),
            Calendar[DateColumn] >= your_variable
                && Calendar[DateColumn] <= TODAY ()
        )
    )
RETURN
    Result

Rate this post