Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

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 w...
Need Help
  • amitchandak's avatar
    amitchandak
    6 years ago

    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])))
)
Anonymous's avatar
Anonymous
Not applicable
6 years ago

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 IDOffice LocationStart DateEnd Date
12345Cincinnati2020-01-28 08:39:18.68306912020-01-28 08:50:57.5322207
21344Lebanon2020-01-28 13:38:18.6796558 
22334Chicago2020-01-27 23:12:07.33067782020-01-29 23:29:21.8193973
16678New York2020-01-20 13:56:03.7079458 
Anonymous's avatar
Anonymous
Not applicable
6 years ago

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's avatar
    amitchandak
    Super User
    6 years ago

    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])))
)