topic Vacancies over time in DAX Commands and Tips

Vacancies over time

metcala — Fri, 20 Jan 2023 10:38:31 GMT

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

Re: Vacancies over time

tamerj1 — Fri, 20 Jan 2023 11:06:40 GMT

metcala

Make sure there is no relationship between the two tables

Re: Vacancies over time

metcala — Fri, 20 Jan 2023 11:26:01 GMT

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.

Re: Vacancies over time

tamerj1 — Fri, 20 Jan 2023 12:23:41 GMT

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