Single date picker with calender

[viswaaa]

Hi All,

I have a table where I have students' data.

Here, I have two columns: join date and left date.

I need to create a date slicer that will control the visuals based on these dates.

SO I have lot of visuals using these 2 columns.

I need to show only a single select date picker with calendar only and If I select 27th October I need to show data from starting to  till that date.

How to get this done.

[v-tsaipranay]

Hi @viswaaa ,

We haven’t received an update from you in some time. Could you please let us know if the issue has been resolved?
If you still require support, please let us know, we are happy to assist you.

Thank you.

[v-tsaipranay]

Hi @viswaaa ,

Thanks for reaching out to the Microsoft fabric community forum. Also thank you @rohit1991 , @Anand24 , @Sergii24  for your response.

I wanted to check if you had the opportunity to review the information provided by @Shubham_rai955  . If you still require support, please let us know, we are happy to assist you.

Thank you.

[Shubham_rai955]

To create a single date slicer with a calendar picker in Power BI that filters visuals based on two date columns (Join Date and Left Date) and shows data up to the selected date, you need a supporting date table and a measure for filtering.

Steps to Implement

1. Create a Date Table:
   Create or use an existing date table covering the range of your data. Mark it as a Date Table in Power BI.

2. Add a Single Date Slicer:
   Add a slicer visual on your report page and set it to use the date column from the date table with single select and calendar picker enabled.

3. Create a Filtering Measure:
   Create a measure to check if the selected date is between Join Date and Left Date (or till current if Left Date is blank). For example:

   FilterVisible = VAR SelectedDate = SELECTEDVALUE('DateTable'[Date]) RETURN IF( MIN('Students'[JoinDate]) <= SelectedDate && (MAX('Students'[LeftDate]) >= SelectedDate || ISBLANK(MAX('Students'[LeftDate]))), 1, 0 )

4. Apply Measure as Visual Filter:
   On each visual that uses these dates, apply a visual-level filter where FilterVisible = 1.

Result

• The date slicer acts as a single date calendar picker.

• When a date is selected, all visuals filter students who have joined on or before that date and have not left before that date.

• This approach effectively shows student data "up to" the selected date using one slicer across multiple date columns.

This is a common approach in Power BI for simultaneous filtering on two date ranges using one date picker and measure-driven filtering.

[viswaaa]

Hi Shubham,

This is not working unless I give join between date column and join date.

But I need to make my left date also work.

Please suggest

[Shubham_rai955 · Accepted solution]

You do not need to create a relationship between the date table and your join/left date columns for this scenario to work in Power BI. Creating a relationship only on Join Date will filter the table directly, but you can use a measure-based approach for flexible filtering without relying on relationships to make your left date also work.

Solution: Measure-Based Filtering Without Relationships

• Create a calendar (date) table in your model and mark it as a Date Table.

• Do NOT create a model relationship between your calendar table and your student table on Join Date or Left Date.

• Create a DAX measure that, for each record, checks if the selected date from the slicer falls between Join Date and Left Date (or if Left Date is blank, treats as still active).

• Use the single date picker slicer from the calendar table for user selection.

• Apply this measure as a visual-level filter (= 1) on your table/visuals.

Sample Measure:

ActiveTillSelectedDate = VAR _selDate = SELECTEDVALUE('Calendar'[Date]) RETURN IF( 'StudentData'[Join Date] <= _selDate && (ISBLANK('StudentData'[Left Date]) || 'StudentData'[Left Date] >= _selDate), 1, 0 )

Filter visuals with ActiveTillSelectedDate = 1.

This way, selecting a date from the slicer will show all students who joined on or before that date and are either still active or left after that date, regardless of direct relationships in your data model.​

[rohit1991]

Hi @viswaaa 

You can achieve this easily with one simple date slicer and a DAX measure. The idea is that when you pick a single date (like 27th October), Power BI will show all records that fall between each person’s join date and that selected date, meaning students who were active up to that day.

Could you please try below steps:

1. Make sure you have a proper Date table connected to your model.

2. Create this measure:

ActiveTillSelectedDate =
VAR _selDate = MAX('Date'[Date])
RETURN
IF(
    'StudentData'[Join Date] <= _selDate &&
    (ISBLANK('StudentData'[Left Date]) || 'StudentData'[Left Date] >= _selDate),
    1
)​

3. Add this measure to your visuals and set a filter where ActiveTillSelectedDate = 1.

4. Use a single-select Date slicer from your Date table on the report.

[Anand24]

@viswaaa,
When user selects 27th October, you want to show all data since start to 27th October according to Joining or Left date?

[viswaaa]

Hi Anand24,

Yes

[Sergii24]

Hu @viswaaa, what is your desired result? Can you share a screenshot from a website/build it in PowerPoint?

What is your approach? What have you already tried? What doesn't work?

Answers to these questions will help us to help you 🙂 The community is here to help you, not solve task intead of you 😉