One filter for two date columns in the same table

[obuolys123]

Hello,

I have this data model where I have the name of the employee in one table (Let's say table A)  his employment start date, and the end date in the same table (Table B, Table A and Table B have a relationship by employee ID) and I have a Date Table. What I need is to be able to filter both dates with one filter/slicer. So I need to filter the start and end dates by a Date Table. Data and e.g. below.

Example data.

The idea of the result I need.

What I want is if I choose to filter the year 2021 I get the result where the start OR end date is in the 2021 year.

I know there is no way to make 2 active relationships between 2 columns in one table and one column in another table. I tried to group a slicer, but I get only dates where the start date AND end date are in the 2021 year, which is not what I need.

So maybe someone knows some kind of workaround with USERELATIONSHIP in CALCULATION or any other workaround, where I would be able to filter the data the way I need?

Thank you.

[amitchandak · Accepted solution]

@obuolys123 , On way is independent date table

new measure =
var _max = maxx(allselected(Date1),Date1[Date])
var _min = minx(allselected(Date1),Date1[Date])
return
calculate( sum(Table[Value]), filter('Table', ('Table'[End Date] >=_min || isblank('Table'[End Date])) && 'Table'[Start Date] <=_max))

Also, refer

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

Or the file attached after signature