Hi everyone,

I was hoping to get some help on filtering visuals by date in Power BI Desktop.

I am creating a dashboard with HR data, and one of the pages is looking at attrition rates. To do this, I have created the 4 measures below as described in this blog post:

Hired Employee = CALCULATE(COUNT(Employee[EmployeeId]),USERELATIONSHIP(Employee[LastHireDate],'DateTable'[Date]) )

Terminated Employees = CALCULATE(COUNT(Employee[EmployeeId]),USERELATIONSHIP(Employee[LastTerminationDate],'DateTable'[Date]),not(ISBLANK(Employee[LastTerminationDate])))

Current Employees = CALCULATE(COUNTx(FILTER(Employee,Employee[LastHireDate]<=max('DateTable'[Date]) && (ISBLANK(Employee[LastTerminationDate]) || Employee[LastTerminationDate]>max('DateTable'[Date]))),(Employee[EmployeeId])),CROSSFILTER(Employee[LastHireDate],'DateTable'[Date],None))

Last Period Employee =

var _min_date = minx(all('DateTable'),'DateTable'[Date])

var _Expression=if(ISFILTERED('DateTable'[Month Year]),maxx('DateTable',DATEADD('DateTable'[Date],-1,MONTH)),maxx('DateTable',DATEADD('DateTable'[Date],-1,YEAR)))

Return

CALCULATE(COUNTx(FILTER(Employee,Employee[LastHireDate]<=_Expression && Employee[LastHireDate]>=_min_date && (ISBLANK(Employee[LastTerminationDate]) || Employee[LastTerminationDate]>_Expression)),(Employee[EmployeeId])),CROSSFILTER(Employee[LastHireDate],'DateTable'[Date],None))

Employee Change% = ROUND(if(not(ISBLANK([Last Period Employee])),CALCULATE( (divide([Current Employees],[Last Period Employee]) -1)*100)),2) & "%"

I have an employee fact table & a dimensional date table, joined using inactive relationships between DateTable[Date] and both Employee[LastHireDate] and Employee[LastTerminationDate], as advised in the blog post (see below):

I need to plot a few graphs, an example of one being a line chart showing count of terminated employees over time. Doing so results in the following blank graph:

Similarly, when trying to plot hires by gender in a stacked column chart, it results in a blank graph:

I have tried doing this turning on Employee[LastHireDate] > DateTable[Date] as an active relationship with Employee[LastTerminationDate] > DateTable[Date] as inactive and vice versa, and neither combination seems to work.

I have also tried deleting the inactive Employee[LastHireDate] > DateTable[Date] and Employee[LastTerminationDate]>DateTable[Date] relationships, and created an active relationship between Employee[Date of Birth] and DateTable[Date]. Plotting a bar chart of COUNT(Employee[EmployeeID]) against DateTable[Year] now generates a populated graph.

Would anyone have any idea why this is happening and how I could make the visuals work, ideally using relationships between Employee[LastHireDate]/Employee[LastTerminationDate] and DateTable[Date]?

Many thanks!

Forum Discussion

Filtering Visuals by Date

Date filters for Visualization

Filtered Date

Between date filter with fixed start date and latest end date

Removing date filter

Filters issues in visual objects