Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
obuolys123
Helper I
Helper I

One filter for two date columns in the same table

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.

obuolys123_2-1658126190231.png

 


The idea of the result I need.

obuolys123_1-1658126132080.png

 

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.




 

1 ACCEPTED SOLUTION
amitchandak
Super User
Super User

@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

View solution in original post

1 REPLY 1
amitchandak
Super User
Super User

@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

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors