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

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