Forum Discussion
Helper IVSingle date picker with calender
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.
Super UserTo 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
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.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.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 )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.
Helper IVHi 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
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.