Forum Discussion
New table based on conditional dates
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
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-trend/ba-p/882970
4 Replies
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-trend/ba-p/882970
That has worked beautifully! Thank you very much for the help 🙂 amitchandak
- Having trouble reconciling your expected output to the sample data provided. Is that the full sample data??
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
