Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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%
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.
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
Ask questions in Eventhouse and KQL, Eventstream, and Reflex.
Check out the May 2024 Power BI update to learn about new features.
User | Count |
---|---|
90 | |
80 | |
60 | |
59 | |
58 |
User | Count |
---|---|
157 | |
119 | |
104 | |
77 | |
69 |