Calendar Table

[Anonymous]

hi, 

im new to power bi and this may be an abitious request but i need to make a card visual that shows the active number of employees given at any time using a date slicer. i have a spreadsheet of an employee list with employment start and end dates (active employees have a blank as their end date) 

ive looked online and its suggested to use calendar table and dax. im unsure on how to do this especially for this application. any help is much appreciated. 

[Syndicate_Admin]

mirate is video!!
https://www.youtube.com/watch?v=QuBoJ7vau8w

[ExcelMonke]

Hello,
Whilst yes, in general, it is recommended to have an additional calendar table, your case seems straight forward enough to do without it. Should your use case get more complex, you can always create a calendar table by creating a new Table (under modeling) with the following dax: 

DateTable = 
CALENDAR([your start date],[your end date])

For the count of active number of employees, consider the following simple solution:

ActiveEmployees = 
COUNTBLANK(EmployeeList[end date])

Here, you are basically counting how many rows in the end date column are blank, which by your logic, would indicate one (1) active employee. 

[Anonymous]

thanks a lot for that. how would this work with the date slicer, say if i wanted to find number of employees active a month ago but some of them had left last week?