Hiring Rate Dynamic Calculation

[Anonymous]

I want to calculate the hiring rate as a dynamic measure.

Ex: Hiring rate in 2018 = # of hires in 2018/ [(total hires on 12/31/2017 + Total Hires on 7/31/2018)/2]

Similarly on Sept 1, 2018, the bolded date needs to be changed dynamically to 8/31/2018 and on Oct 1, 2018 it needs to be changed to 09/30/2018 and so on.

For previous years, it needs to be for example, Hiring rate in 2017 = # of Hires in 2017/([total hires on 12/31/2016 + total hires on 12/31/2017]/2)

The denominator part is an average of the count on those two dates. Is there any way this can be wriiten as an expression? Or any other way for this action to be performed?

Thank You!

[Anonymous · Accepted solution]

@Anonymous create a measure something like this

HiringRate = 

VAR EmpCY = Calculate(Count of employees, filter(where date is max date in dataset & year = year(date field))

VAR EmpPY = Calculate(Count of employees, filter(where date is max date in dataset in previous year & year = year(date field)-1)

RETURN EmpCY/((EmpPY+EmpCY)/2)

If you need more clarifications/exact formula, please post same dataset and power bi file.

[Anonymous]

Thank you! I also tried creating a date table and linking all my dates there. That also worked.