Counting rows before the selected period in slicer

[sjayawardana]

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,

[amitchandak]

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

[sjayawardana]

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)

[sjayawardana]

Thanks for the reply@amitchandak is 'Date'[Published date] the published date in the fact table?