Filtering Visuals by Date

[megane123]

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!

[Ashish_Mathur]

Hi,

Such problems can be solved only by working on the PBI file.

[megane123]

Hi Ashish,

Is there any way I can share a pbix file that contains confidential company information?

Many thanks,

Megan

[Ashish_Mathur]

Hi,

Anonymise the data and share the download link here.

[megane123]

Great, thank you Ashish! I have saved the pbix to OneDrive here https://1drv.ms/u/s!At6A-s7ZjtYSgRTF_RNRwcPJHjUi?e=GGtO6v

Many thanks,

Megan

[Ashish_Mathur]

Hi,

On the Attrition Analysis page, you are showing Employee Change% in the Tooltips section and nothing in the Values section.  Move that measure to the Values section.  I tried doing so but i could not.  Your measures which feed into calculating the Employee Change% measure are working fine because they can be dragged to the Values section and propoerly show up as columns in the chart. 

[DA1981]

Is there a problem with your date in your fact table? Is it joining correctly to you date table?