Start and End date as current date in default with history date as well, date slicer between option

[Nanda047]

Start date and End date as current date in default (Whenever user opens the report) and also the user should able to select history date as well in same date slicer (Between option).
Have tried with bookmarks, but start date is not changing to current date as it is showing the same date when bookmark is saved.

Please help me to resolve this.

[v-ssriganesh]

Hi @Nanda047,
I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.
Thank you.

[v-ssriganesh]

Hi @Nanda047,

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[v-ssriganesh · Accepted solution]

Hi @Nanda047,
Thank you for reaching out to the Microsoft Fabric Forum Community.

I have reproduced your scenario using sample data and created a Power BI report to match your requirements. I’m attaching the screenshot of the expected output and the .pbix file for your reference.

Steps I Followed:

• Created the SalesData table using the following DAX:

SalesData =

DATATABLE(

    "SalesID", INTEGER,

    "SalesAmount", CURRENCY,

    "SalesDate", DATETIME,

    {

        {1, 100, "2024-03-20"},

        {2, 150, "2024-03-21"},

        {3, 200, "2024-03-22"},

        {4, 130, "2024-03-23"},

        {5, 175, "2024-03-24"},

        {6, 90, "2024-03-25"}

    }

)

• Created a DateSelection table to allow users to select a date range:

DateSelection =

VAR TodayDate = TODAY()

RETURN

ADDCOLUMNS(

    CALENDAR(DATE(2024,3,1), TodayDate),

    "IsCurrent", IF([Date] = TodayDate, 1, 0)

)

Created a measure to filter sales data based on the selected date range:
SelectedDateFilter =

VAR MinDate = MIN('DateSelection'[Date])

VAR MaxDate = MAX('DateSelection'[Date])

RETURN

IF(

    MAX('SalesData'[SalesDate]) >= MinDate && MAX('SalesData'[SalesDate]) <= MaxDate,

    1,

    0

)

• Added a Table Visual with SalesData[SalesID], SalesData[SalesAmount], and SalesData[SalesDate] and Added a Between Date Slicer using DateSelection[Date].
• Applied a visual filter on the table using the SelectedDateFilter measure (Set to "is 1").

Now, the report dynamically updates based on the selected date range.

Screenshot of Expected Output:

Thanks you, @danextian & @pankajnamekar25 for sharing valuable insights.

If this information is helpful, please “Accept it as a solution” and give a "kudos" to assist other community members in resolving similar issues more efficiently.
Thank you.

[v-ssriganesh]

Hi @Nanda047,
I hope this information is helpful. Please let me know if you have any further questions or if you'd like to discuss this further. If this answers your question, please accept it as a solution and give it a 'Kudos' so other community members with similar problems can find a solution faster.
Thank you.

[danextian]

Hi @Nanda047 

If you really want the current to be selected as the default date and not some other column that has the word "current date"  as the slicer selection, you will need to need to leverage the Group by Columns property to "store a filter by using an alternate value, which represents the key of the entity." Power BI uses this key to store the filter, allowing the corresponding filter value in the visual to change dynamically. For instance, if May-24 is selected in the visual and currently has a key of 0, when June arrives, May will shift to a key of 1, and June will take the key of 0. Consequently, the slicer selection will automatically change to June. This can be done using Tabular Editor, an external tool.

Further reading can be found in the video description-  https://youtu.be/MrEAZREQuXM?si=q0JGyYRUbl1qvjjv

Note: Persistent filters, if enabled, will always override the default selection.

[Nanda047]

Hi @danextian 

My requirement is to show it in Between date slicer option not in dropdown. Could you please help me on this?

[pankajnamekar25]

Hello @Nanda047 

To set the Start and End Date as the current date by default in a Between slicer, while still allowing users to select historical dates, you need a dynamic approach since bookmarks store static values. Instead of bookmarks, create a Date Table using DAX with dates up to today and a flag for today’s date. Then, define measures for DefaultStartDate and DefaultEndDate as TODAY().

Since Power BI doesn't allow dynamic default values in a Between slicer, you can use a Relative Date Slicer set to "Last 0 days" to default to today while keeping it flexible for users. Alternatively, apply a Relative Date Filter in the Filter Pane to pre-filter to today, allowing users to remove it for historical selection. This ensures the slicer starts with today’s date but remains interactive

Thanks,
Pankaj

If this solution helps, please accept it and give a kudos, it would be greatly appreciated.