Vacancies over time

[metcala]

Hi all

Still pretty new to PowerBI and I am really struggling to create a solution to display vacancies over time. I have a working solution tracking vacancies using today's date however I am looking to build a solution using a date slicer to select any date and look a the position.

Current Data Structure

Staffing table

EmployeeID/VacancyID (all Employee IDs start with 1)

Name

Team

Role

Start Date

End Date

Date table

Date

Current Measure

Vacancies

=CALCULATE(
   COUNTA('Staffing'[EmployeeID/VacancyID]),
   FILTER('Staffing',LEFT('Staffing'[EmployeeID/VacancyID],1)<>"1",
   FILTER('Staffing','Staffing'[Start Date]<TODAY() && 'Staffing'[End Date]>TODAY())
)

Proposed Solution

I have tried replacing TODAY() with Date[Date] and adding a slicer with the [Date] however that doesn't work.

Any help or guidance would be really appreciated!

Thanks

[tamerj1 · Accepted solution]

Hi @metcala 

please try

=
VAR SelectedDate =
MAX ( 'Date'[Date] )
RETURN
CALCULATE (
COUNTA ( 'Staffing'[EmployeeID/VacancyID] ),
FILTER (
'Staffing',
LEFT ( 'Staffing'[EmployeeID/VacancyID], 1 ) <> "1"
&& 'Staffing'[Start Date] < SelectedDate
&& 'Staffing'[End Date] > SelectedDate
)
)

[tamerj1]

@metcala 

Make sure there is no relationship between the two tables

[metcala]

@tamerj1 Thanks for the quick reply. I have just deleted all of the relationships between the Staffing table and Date table and I am still getting "Can not find name [Date]" in the below measure.

=CALCULATE(
   COUNTA('Staffing'[EmployeeID/VacancyID]),
   FILTER('Staffing',LEFT('Staffing'[EmployeeID/VacancyID],1)<>"1",
   FILTER('Staffing','Staffing'[Start Date]<'Date'[Date] && 'Staffing'[End Date]>'Date'[Date])
)