Forum Discussion
Get data using date slicer between two datetime fields even if one of them is empty
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.
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]))) )
4 Replies
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 LinkedinTo 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 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 ])))