Measure OR Calculated column based on the Date selected in slicer

[Ramees_123]

Below is the relationship with my employees and date table. 

Start Date of employment has active relationship and last date of employnment has inactive relationship.

I have a date hierarchy slicer where I could select whole year, quater, month and date too.

When I select a date, I need to get the count of employees based on below formula. 

Start Date <= Slicer Selected Date

Last Date >= Slicer Selected Date OR Last Date is Blank.

This should work even when multiple dates are selected in the slicer.

How to do this ? Please help.

[TomMartens · Accepted solution]

Hey @Ramees_123 , the challenge you are facing has a name "events-in-progress", this article Events-In-Progress | Gerhard Brueckl on BI & Data (gbrueckl.at) provides a solution to a special requirement but also contains links to other articles.

I recommend starting with the article by Jason Thomas.

If the articles are not sufficient, create a pbix that contains sample data and upload the pbix to onedrive or dropbox and share the link. If you are using xlsx to create the sample data instead of manual input, share the xlsx as well. Please also describe why the approach described in the articles did not work. Make sure that you reference the sample data when you describe the expected result, especially when you are using more than one date..

Regards,

Tom