Get data using date slicer between two datetime fields even if one of them is empty

[Anonymous]

Hi,

I am trying to write a query to get data between to date fields which is start date and end date using a date slicer as a filter. The catch here is that end date can be null.

I tried to write a column and mesaure too, but I am not getting the desired data.

I would be really grateful if anyone can help.

[amitchandak]

Refer , if one of these can help you

HR-Analytics-Active-Employee-Hire-and-Termination-trend

Comparing-Data-Across-Date-Ranges

Provide some more details, like what kind of data and final outcome

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
Winner-Topper-on-Map-How-to-Color-States-on-a-Map-with-Winners , HR-Analytics-Active-Employee-Hire-and-Termination-trend
Power-BI-Working-with-Non-Standard-Time-Periods And Comparing-Data-Across-Date-Ranges

Connect on Linkedin

[Anonymous]

To provide more context to the problem I am facing. 

I want to display the following information to my operations team, in form of a table. For eg, if there was employee with Employee ID "12345" who was at Cincinnati office just for some time and then came back to different location. The problem, I am facing is right now when using a date slicer to display the following information in a table between ceratin date range, it's not giving me the desired information. Either it won't give me the employee ID of the employee who was in the office just for a day or it will not give me null value rows.

This is an urgent issue for me to solve. Please do help me.

Employee ID	Office Location	Start Date	End Date
12345	Cincinnati	2020-01-28 08:39:18.6830691	2020-01-28 08:50:57.5322207
21344	Lebanon	2020-01-28 13:38:18.6796558
22334	Chicago	2020-01-27 23:12:07.3306778	2020-01-29 23:29:21.8193973
16678	New York	2020-01-20 13:56:03.7079458

[Anonymous]

Is it possible to get distinct "Employee iD" after applying the date filter?

BTW, I used this and it worked for me. 

Current Employees = CALCULATE(COUNTx(FILTER(Employee,Employee[Start Date]<=max('Date'[Date]) && (ISBLANK(Employee[End Date]) 
|| Employee[End Date]>max('Date'[Date]))),(Employee[Employee Id ])))

 @amitchandak 

[amitchandak · Accepted solution]

Try

Current Employees = CALCULATE(distinctcount(Employee[Employee Id ]),FILTER(Employee,Employee[Start Date]<=max('Date'[Date]) && (ISBLANK(Employee[End Date]) 
|| Employee[End Date]>max('Date'[Date])))
)
Or
Current Employees = CALCULATE(distinctcount(Employee[Employee Id ]),all('Date'),FILTER(Employee,Employee[Start Date]<=max('Date'[Date]) && (ISBLANK(Employee[End Date]) 
|| Employee[End Date]>max('Date'[Date])))
)