New table based on conditional dates

[Joshwaaa]

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

[Greg_Deckler]

Having trouble reconciling your expected output to the sample data provided. Is that the full sample data??

[Joshwaaa]

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

[amitchandak · Accepted 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...

[Joshwaaa]

That has worked beautifully! Thank you very much for the help 🙂 @amitchandak