Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Good afternoon all
Hope you are keeping safe
I am looking to create a new table based on values in 2 other tables but I am struggling to identify the correct format. I have a table set up as (amongst other fields)
Org Level 2 | Candidate Name | Candidate Submit Date | Candidate Shortlist Date | Candidate Interview Date |
Org UK | Barry | 1/1/20 | 1/2/20 | 1/3/20 |
Org UK | Bruce | 1/2/20 | 1/3/20 | 1/4/20 |
What I then want to is create a new table with the org level 2 as the initial column, then the following columns count the number of candidates in the respective following columns, based on their date.
So for March I would want to see
Org Level 2 | # Candidates Submit | # Candidates Shortlist | # Candidates Interview |
ORG UK | 3 | 2 | 1 |
ORG Spain | 45 | 2 | 43 |
Where each column shows the relevant data for the month. So that 3 would be three candidates submitted between 01 March and 31 March
Hopefully I make sense and someone able to offer advice!
Thank you
Josh
Solved! Go to Solution.
Create a date table and join it with all three dates. One will active and two will be inactive. Use userelation to use the other dates
Try like
# Candidates Submit = CALCULATE(COUNT(Table[Candidate Name]),USERELATIONSHIP(Table[Candidate Submit Date],'Date'[Date]) )
# Candidates Shortlist = CALCULATE(COUNT(Table[Candidate Name]),USERELATIONSHIP(Table[Candidate Shortlist Date],'Date'[Date]) )
# Candidates Interview = CALCULATE(COUNT(Table[Candidate Name]),USERELATIONSHIP(Table[Candidate Interview Date],'Date'[Date]) )
Refer this blog, how it works: https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...
Hi @Greg_Deckler thank you for the reply
This was not, this was just a mock up I created, due to the nature of the data I am working with I was not able to provide actual data
Create a date table and join it with all three dates. One will active and two will be inactive. Use userelation to use the other dates
Try like
# Candidates Submit = CALCULATE(COUNT(Table[Candidate Name]),USERELATIONSHIP(Table[Candidate Submit Date],'Date'[Date]) )
# Candidates Shortlist = CALCULATE(COUNT(Table[Candidate Name]),USERELATIONSHIP(Table[Candidate Shortlist Date],'Date'[Date]) )
# Candidates Interview = CALCULATE(COUNT(Table[Candidate Name]),USERELATIONSHIP(Table[Candidate Interview Date],'Date'[Date]) )
Refer this blog, how it works: https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
99 | |
80 | |
64 | |
57 |
User | Count |
---|---|
145 | |
111 | |
92 | |
84 | |
66 |