I need help with dynamically filtering my Power BI visuals based on the [Start Date] and [End Date] that a user enters into a slicer.
My data model is very simple. I have one fact table that consists of distinct worker records where each worker has a [start date] and an [end date] of when the worker started and finished their work assignment.
And there's a calendar table linked to this fact table.
What I want is to be able to enter a [start date] and an [end date] range into a slicer and then based on these dates the worker records would be filtered down based on the worker records’ [start date] and [end date] to only those worker records that would be considered “active” during the date range entered. So for instance, let's say you enter a [start date] of 1/1/2023 and an [end date] of 3/31/2023 into the slicer date range. So then the worker records where the worker has a [start date] of less than or equal to the slicer’s [end date] and a worker [end date] greater than the slicer’s [start date] than the worker record would be considered “active” during the date range entered and would be displayed in my various PBI visuals.
I thought I could create [start date] and [end date] parameters that could be fed by slicer entires and these parameters could be input into a DAX formula that dynamical populates a custom column in my fact table that populates with “Active Worker” or “Inactive Worker” values. However, I keep encountering various errors.
Can anyone please assist this Power BI novice with this scenario? I'd be very grateful! Thank you PBI Community!
