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 UserHi 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.