Dynamically Filtering PBI Visuals based on date range

[Bali2022]

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!

[Ashish_Mathur · Accepted solution]

Hi,

I have solved a similar problem in the attached PBI file.

Hope this helps.

[Bali2022]

Thank you for sharing this Ashish! 

[Ashish_Mathur]

You are welcome.