Fill Rate Calculation - Placements added after 2 months

[SPalmitos]

Good day everyone,

Hope your week is off to a good start.

I need to calculate the fill rate of job vacancies [Total positions filled / Total job openings].

However, instead of using the placements for the same month, I want to use the placements that were added 2 months later.

So, for example, jobs added in January 2022 will be used for total job openings and then, for placements, using those added in March.

I hope this is clear?

Jobs Added List Sample

Job ID	Created Date
64185	14/03/2022
64197	16/03/2022
64594	09/05/2022
70443	06/04/2023
68156	11/09/2022
65676	15/07/2022
70459	11/04/2023
69730	20/01/2023
69731	20/01/2023
70094	23/02/2023
70522	20/04/2023
65125	31/05/2022
65745	22/07/2022
70178	02/03/2023
68240	20/09/2022
65227	09/06/2022
65148	01/06/2022
63955	08/02/2022
65517	27/06/2022
69897	06/02/2023
62762	05/01/2022
64075	27/02/2022
64450	20/04/2022
64619	12/05/2022
64620	12/05/2022
65472	23/06/2022
65709	18/07/2022
65710	18/07/2022
65867	27/07/2022
65868	27/07/2022
66019	28/07/2022
66092	03/08/2022
66093	03/08/2022
66102	04/08/2022
66131	08/08/2022
66150	10/08/2022

Placement List Sample

Job ID	Placement ID	Date Added
64185	12112	14/03/2022
64594	12682	29/07/2022
62775	11968	31/01/2022
62775	11975	31/01/2022
62775	12184	31/03/2022
70094	13478	12/04/2023
70258	13526	04/05/2023
65745	12759	26/08/2022
63955	12009	08/02/2022
63955	12027	10/02/2022
63955	12050	21/02/2022
63955	12100	09/03/2022
63955	12483	14/06/2022
63955	12866	12/09/2022
63955	13117	30/11/2022
65517	12520	27/06/2022
69897	13493	19/04/2023
69217	13737	01/01/2023
69217	13665	01/02/2023
69217	13461	01/03/2023
69217	13556	01/04/2023
69217	13651	01/05/2023
62950	12067	28/02/2022
64450	12221	20/04/2022
64619	12299	12/05/2022
64620	12300	12/05/2022
65709	12726	15/08/2022
66150	12983	24/10/2022
68791	13505	27/04/2023
70193	13612	31/05/2023
95	11528	12/01/2022
95	11530	12/01/2022
95	11531	12/01/2022
95	11532	12/01/2022
95	11533	12/01/2022
95	11534	12/01/2022

[v-tangjie-msft · Accepted solution]

Hi @SPalmitos ,

According to your description, here are my steps you can follow as a solution.

(1)My test data is the same as yours.

(2) We can create a column.

month = FORMAT('PowerBI Jobs Added'[Created Date],"yyyy-mm")

(3)We can create measures. 

Total job openings = CALCULATE(COUNTROWS(ALLSELECTED('PowerBI Jobs Added')),'PowerBI Jobs Added'[month] in VALUES('PowerBI Jobs Added'[month]))

Total positions filled = COUNTROWS(FILTER(ALLSELECTED('PowerBI Placements'),EOMONTH('PowerBI Placements'[Date Added],0)=EOMONTH(MAX('PowerBI Jobs Added'[Created Date]),2)))

the fill rate of job vacancies = DIVIDE('PowerBI Jobs Added'[Total positions filled],'PowerBI Jobs Added'[Total job openings])

(4) Then the result is as follows.

If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. Thank you.

Best Regards,

Neeko Tang

If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly. 

[SPalmitos]

Files Sample 

See link for the files.