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

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