cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Frequent Visitor

Counting rows before the selected period in slicer

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,

3 REPLIES 3
Super User IV
Super User IV

@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"))



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

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?

Helpful resources

Announcements
secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

Wave Release 2

Check out the updates in Power BI.

Overview of Power BI 2020 release wave 2!

Microsoft Ignite

Microsoft Ignite

Join digitally, March 2–4, 2021 to explore new tech that's ready to implement. Experience the keynote in mixed reality through AltspaceVR!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.