Date Slicers

[SudhanshuD4512]

I have a requirement of having a date slicer like (from date) to (to date). Also there should be option like start of month (Like Dec1) to Today Date (10 Dec). Also we should be able to use custom date as well. I need it in form of drop down. Is it achievable?

[srlabhe · Accepted solution]

Yes, this is achievable in Power BI by combining standard slicer features, Relative Date Slicers, and potentially some DAX measures for truly dynamic defaults, allowing a user-friendly date range (From/To, Month-to-Date to Today) in a dropdown or slider format, with custom date entry. You can use the "Between" mode for From/To, "Relative Date" for preset ranges like "This Month," and even create custom date columns or measures to handle "Start of Month to Today" dynamically. 

How to Achieve This

1. Create the Base Slicer:
   • Add a Slicer visual to your report.
   • Drag your Date field (from a proper Date Table) into the Field well.
   • In the Format pane, under Slicer settings, set the Style to Between for a standard From/To range.
2. Implement Relative Date Options (Month-to-Date to Today):
   • Change the Slicer Style to Relative Date.
   • Select options like "This month" to get Month-to-Date (MTD) to Today automatically.
3. Enable Custom Date Entry:
   • For the "Between" style, users can manually input start and end dates.
   • For more advanced control (like ensuring the end date is always today or end of month), you'll need DAX:
     • Create a calculated column for "Start of Current Month" in your Date Table (e.g., DATE(YEAR([Date]),MONTH([Date]),1)).
     • Use measures to dynamically filter or set default values for the slicer, as shown in community solutions for setting defaults to today or start of month.
4. Combine and Customize (Advanced):
   • Use a Disconnected Table for predefined options (like "This Month," "Last 7 Days") and another slicer for custom dates, linked via measures.
   • You can even build a dynamic title that changes based on the selection to guide the user. 

In essence, use the built-in "Between" for custom ranges, "Relative Date" for fixed relative periods (like MTD), and DAX with an independent Date Table for highly dynamic "Start of Month to Today" functionality. 

[Anonymous]

Hi @SudhanshuD4512,

Just following up to see if the Response provided by community members were helpful in addressing the issue. if the issue still persists Feel free to reach out if you need any further clarification or assistance.

Best regards,
Prasanna Kumar

[Anonymous]

Hi @SudhanshuD4512,

Thank you for reaching out to the Microsoft Fabric Forum Community, and special thanks to @Anand24 and @srlabhe  for prompt and helpful responses.

Just following up to see if the Response provided by community members were helpful in addressing the issue. if the issue still persists Feel free to reach out if you need any further clarification or assistance.

Best regards,
Prasanna Kumar

[Anand24 · Accepted solution]

Hi @SudhanshuD4512,
All of these in a single slicer is not possible for Power BI. You can have only one of the 3 (between, relative, dropdown) in a single slicer by choosing the date slicer style:

Between for Stard and End Date.
Relative Date for MTD (Start of Month to today).

Dropdown for custom date selection in dropdown.

The only 2 work-arounds I can think of are:
1. Instead of slicer, use date as a visual/page/report level filter and make it visible for the users. Users can select type of slicer for them. Easiest and best work-around IMO.

2. Create a button bookmark and let show each slicer based on user's button selection. You will have to play very carefully with edit interactions though for this one to succeed.

Give a Thumbs Up if this post helped you in any way and Mark This Post as Solution if it solved your query !!! Proud To Be a Super User !!! LinkedIn

[srlabhe · Accepted solution]

Yes, this is achievable in Power BI by combining standard slicer features, Relative Date Slicers, and potentially some DAX measures for truly dynamic defaults, allowing a user-friendly date range (From/To, Month-to-Date to Today) in a dropdown or slider format, with custom date entry. You can use the "Between" mode for From/To, "Relative Date" for preset ranges like "This Month," and even create custom date columns or measures to handle "Start of Month to Today" dynamically. 

How to Achieve This

1. Create the Base Slicer:
   • Add a Slicer visual to your report.
   • Drag your Date field (from a proper Date Table) into the Field well.
   • In the Format pane, under Slicer settings, set the Style to Between for a standard From/To range.
2. Implement Relative Date Options (Month-to-Date to Today):
   • Change the Slicer Style to Relative Date.
   • Select options like "This month" to get Month-to-Date (MTD) to Today automatically.
3. Enable Custom Date Entry:
   • For the "Between" style, users can manually input start and end dates.
   • For more advanced control (like ensuring the end date is always today or end of month), you'll need DAX:
     • Create a calculated column for "Start of Current Month" in your Date Table (e.g., DATE(YEAR([Date]),MONTH([Date]),1)).
     • Use measures to dynamically filter or set default values for the slicer, as shown in community solutions for setting defaults to today or start of month.
4. Combine and Customize (Advanced):
   • Use a Disconnected Table for predefined options (like "This Month," "Last 7 Days") and another slicer for custom dates, linked via measures.
   • You can even build a dynamic title that changes based on the selection to guide the user. 

In essence, use the built-in "Between" for custom ranges, "Relative Date" for fixed relative periods (like MTD), and DAX with an independent Date Table for highly dynamic "Start of Month to Today" functionality.