PowerBI fixing custom start date for Date Range slicer
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