Slicer - start and end dates

[Class66Loco]

Hello,

I have a table with start date and end date columns. 

I've created a calendar table using these two date columns using MIN and MAX from the two columns so I've effectively got the entire date range. 

Can I create a single date slicer (between dates style) where the start and end date columns reflect the selection from the slicer? Do I need to create separate start and end date calendar tables and use two separate slicers? 

Edit: I've created a man to one relationship between the end date column in the main table and the date column in the calendar table. This seems to create the effect I'm after for returning end date results in the end date column so I'm looking to add the start date column in a similar fashion. 

Thank you.

[FarhanJeelani · Accepted solution]

Hi @Class66Loco ,

Yes, you can achieve this without needing two separate slicers for the start and end dates. Here's how you can set it up:

1. Single Calendar Table: Since you've already created a calendar table using the MIN and MAX from your start and end date columns, you're on the right track. Ensure this table has a continuous range of dates covering the entire span of both columns.

2. Relationships:

   • You can create two separate relationships between your calendar table and the main table:
     • One active relationship between the End Date column in your main table and the Date column in the calendar.
     • One inactive relationship between the Start Date column in your main table and the Date column in the calendar.
   • The inactive relationship can be used dynamically in your measures.

3. Single Date Slicer:

   • Add a slicer to your report using the Date column from the calendar table.
   • Set the slicer to "Between" for a range selection.

4. Dynamic Measures:

   • To reflect the selected range in your visuals, use measures with USERELATIONSHIP to activate the appropriate relationship as needed. For example:

   Selected Start Date = 
   CALCULATE(
       MIN(MainTable[Start Date]),
       USERELATIONSHIP(Calendar[Date], MainTable[Start Date])
   )
   
   Selected End Date = 
   CALCULATE(
       MAX(MainTable[End Date]),
       USERELATIONSHIP(Calendar[Date], MainTable[End Date])
   )

   • These measures will dynamically calculate the start and end dates based on the slicer selection.

5. Visuals and Logic:

   • Use these measures in your visuals to display or filter data based on the selected date range.

This setup allows you to maintain a single slicer while still reflecting the selection in both the start and end date columns. Let me know if you need further clarification! 😊

Please mark this as solution if it helps. Appreciate Kudos

[Ritaf1983]

Hi @Class66Loco 
Please refer to the linked tutorial :
https://www.youtube.com/watch?v=pQ9eSnfAhnc

if it won't help please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
Do not include sensitive information. Do not include anything that is unrelated to the issue or question.
Please show the expected outcome based on the sample data you provided.

Need help uploading data? https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...
Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.