Calculate hiring trend by state - with status start date and status end date

[TMN_help]

Hello, I need to calculate Hiring Trend (%) by State. I have 2 tables - A candidate table and a Date table.

Candidate Table

ID	Name	Status Start Date	Status End Date	Status Name	Job Name	State
01	Adam	10/17/22	10/30/22	Hired	Manager	CA
01	Adam	10/10/22	10/17/22	Offered	Manager	CA
01	Adam	9/30/22	10/10/22	Interview	Manager	CA
01	Adam	9/15/22	9/30/22	New	Manager	CA
02	Dona	10/20/22	blank	Hired	Assistant	MD
02	Dona	10/01/22	10/05/22	New	Assistant	MD
03	Emily	10/15/22	blank	Hired	Accountant	CA
04	Thomas	9/28/22	10/7/22	Interview	Driver	TX
05	Patrick	9/20/22	blank	Declined	Developer	OH
06	Zoe	9/01/22	10/14/22	Hired	Analyst	AZ

Date Table

Date	Year	WeekofYear	WeekBeginDate	WeekEndDate
10/15/22	2022	42	10/9/22	10/15/22
10/16/22	2022	43	10/16/22	10/22/22
10/17/22	2022	43	10/16/22	10/22/22

I want to calculate Trend (%)  as Number of Hires (candidate with status name "Hired") for previous week (week starts with Sunday) divided by AVERAGE for prior 3 weeks.

Example: Number of Hires for week ending Oct 29 / AVERAGE (Number of Hires for week ending Oct 8; Number of Hires for week ending Oct 15; Number of Hires for week ending Oct 22).

The final visual should be US map.

Green: 100% or more

Yellow: 90-99%

Red: < 90%

[v-xiaotang]

Hi @TMN_help 

Thanks for reaching out to us.

>> divided by AVERAGE for prior 3 weeks.

could you explain this with an example?

Best Regards,

Community Support Team _Tang

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

[TMN_help]

Sure.

For example - Let's look at the Total Hires by Week (the date in the bracket is WeekEndDate)

Total Hires (Oct-29) = 55

Total Hires (Oct-22) = 48

Total Hires (Oct-15) = 50

Total Hires (Oct-8) = 51

Trend % = 55 / Average(48, 50, 51)

Trend % = 110% as number of hires increased which is obiously positive and will show it geen in my map visual