1

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.

Mano
  • 13
  • 2
  • Define your range in a measure directly! then you can set the requested limits! At the end, It will get those dates directly from your date tables which also contain full range of dates(including old) – Ozan Sen Dec 06 '22 at 12:37

1 Answers1

0

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
Ozan Sen
  • 2,477
  • 2
  • 4
  • 16
  • I have created a measure as mentioned below. Can you please guide me how can I put the condition for start date keeping older date enabled. Calendar = var _cal = CALENDAR(MIN(email_notification_info[email_notification_sent], TODAY()) var _result = ADDCOLUMNS( _cal, ..... ..... ) return _result – Mano Dec 06 '22 at 16:33
  • @Mano I updated my post with your request! Please check it there! – Ozan Sen Dec 06 '22 at 17:08
  • Thanks for the super quick response. Let me try the above solution. – Mano Dec 06 '22 at 17:38