Building visualizations - linking turnover data tables

[ejordan95]

Hi all, 

I'm stuck.  

BACKGROUND:

I'm building out a visual, that shows the # of job requisitions that were open every month.  This is NOT the number of jobs whose open data was in any given month.  

I have a data set with open date, close date, job level, location, recruiter, and job requisition ID.  

I have a seperate table with columns titled Month and "count of open requsitions".  

The formula in excel in each row next to the corresponding month is: =COUNTIFS(Table4[Requisition Requisition Open Date],"<="&[@Month],Table4[Requisition Requisition Close Date],">"&[@Month])+COUNTIFS(Table4[Requisition Requisition Open Date],"<="&[@Month],Table4[Requisition Requisition Close Date],"")

This gets me all jobs that have an open date on or before (ex.) 01/31/2021, with a close date after 01/31/2021 AND all jobs that have an open date on or before 01/31/2021 and have a close date that is blank.  

THE ISSUE: 

In excel, I can obviously make a visual with the month/ count table and get me what I need (vis. below).  However, I cannot add slicers for recruiter, job level, or location.  It filters the original data set but the calculations are not counting the subtotal, so the table with calculations does not update.  

What I need is in PowerBI (since we're moving the dashboard over there,) to create the same visual, but somehow link (?) the two tables so that when we use the slicers it affects the calculated table so the chart reflects the filter.  

e.g.  # of jobs that were open on any given month for job level 6.  

I am extremely open to suggestions and appreciate any support you can give, articles or guidance from your own minds would work well! 

(hope this makes sense!) 

Best, 

[Anonymous]

Hi @ejordan95 ,

Using SELECTEDVALUE() function you could get the selected value in the slicer and you could add it to the formula.

If you want us to help with the DAX formula, please share some sample data and excepted result.

https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523 

Best Regrads,

Jay

[amitchandak]

@ejordan95 , with two date like open and close same like a similar problem like HR

See if this blog can help https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...