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.
Hi,
My Fact table looks like below:
Candidate Name | Status | Recruiter Full Name | Job Ad Published date | Timestamp | Position |
Candidate 1 | New | PB | 01/01/2020 | 02/01/2020 | Support Worker |
Candidate 1 | Phone screened | KW | 01/01/2020 | 05/01/2020 | Support Worker |
Candidate 1 | Interview complete | PB | 01/01/2020 | 24/01/2020 | Support Worker |
Candidate 1 | Offered role | KW | 01/01/2020 | 22/02/2020 | Support Worker |
|
|
|
|
|
|
Candidate 2 | New | PB | 01/01/2020 | 03/01/2020 | Admin Officer |
Candidate 2 | Phone screened | KW | 01/01/2020 | 05/01/2020 | Admin Officer |
Candidate 2 | Interview complete | PB | 01/01/2020 | 25/01/2020 | Admin Officer |
Candidate 2 | Offered role | PB | 01/01/2020 | 30/01/2020 | Admin Officer |
|
|
|
|
|
|
|
|
|
|
|
|
Candidate 3 | New | PB | 01/01/2020 | 03/01/2020 | Finance Officer |
Candidate 3 | Phone screened | KW | 01/01/2020 | 05/01/2020 | Finance Officer |
Candidate 3 | Interview complete | PB | 01/01/2020 | 25/01/2020 | Finance Officer |
Candidate 3 | Offered role | KW | 01/01/2020 | 30/01/2020 | Finance Officer |
I need to calculate the following conversion rate for each position
Formula is : Conv.rate per position =candidates offered role in current period/(interview completed in current period +previous periods)
There is a slicer containing the Offered role date(Date hierarchy – Year, Month)
Need the # of candidates that were Status=Interview completed before the slicer Period+Slicer period(Before selected offered Period-Month)
Need the # of candidates that were status =Interview booked before the slicer period+Slicer period
Need the # of candidates that were status=Phone Screened before the slicer period+Slicer period
Need the # of candidates that were status=Offered role within the period selected in the slicer containing the Offered role date.
Candidate name is considered unique.
Appreciate all the help,
Thanks,
@sjayawardana , Create an independent Date table and try measures like
Measure =
var _max = maxx(allselected('Date') , 'Date'[Date]) // Before and selected period
var _min = minx(allselected('Date') , 'Date'[Date]) // before selected period <
return
calculate(distinctcount('Table'[Candidate]), Filter(all('Table'), 'Date'[Published date] <=_max && 'Table'[Status] = " Interview complete"))
Measure =
var _max = maxx(allselected('Date') , 'Date'[Date]) // Before and selected period
var _min = minx(allselected('Date') , 'Date'[Date]) // before selected period <
return
calculate(distinctcount('Table'[Candidate]), Filter(all('Table'), 'Date'[Published date] <=_max && 'Table'[Status] = " Phone screened"))
Could you help me with a logic change? Conv.Rate should be= Candidates with offered Roles #(all roles offered within the current month)/Candidates with Interviews completed#(only for the offered positions in the numerator)
Thanks for the reply@amitchandak is 'Date'[Published date] the published date in the fact table?
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 |
---|---|
47 | |
26 | |
19 | |
14 | |
10 |
User | Count |
---|---|
58 | |
50 | |
44 | |
19 | |
18 |