Forum Discussion

megane123's avatar
megane123
Frequent Visitor
5 years ago

Filtering Visuals by Date

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!

6 Replies

  • DA1981's avatar
    DA1981
    Frequent Visitor

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

    • megane123's avatar
      megane123
      Frequent Visitor

      Hi Ashish,

       

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

       

      Many thanks,

      Megan