How to calculate estimated Recruitment / Hiring Date, depending on certain conditions

[marellis]

Hi

There are two things that I'd like to calculated - based on a recruitment plan for this year.

1) What is the "estimated offer date" - i.e. after we have opened a hiring request, what is the estimated date on which we will find a candidate and make them on offer.  We are estimating this will be "Requisition approval date" (date hiring request approved) + 75 calendar days (estimated time to find a candidate and make them an offer).

2) After we have calculated the above estimated offer date, I then need to calculate what is the "estimated onboarding date" (i.e. their start date).  This is a more complex calculation, because it depends on the hiring "Region" of the requistion/candidate.   We have estimated time per onboarding (candidate notice periods) per region as follows:

Europe - estimated offer date + 90 calendar days = estimated onboarding date

APJ -  estimated offer date + 60 calendar days = estimated onboarding date

NAM -  estimated offer date + 30 calendar days = estimated onboarding date

So for the above, I need DAX to work out e.g.  "If hiring region column = Europe", then estimated onboarding date = "estimated offer date + 90 days",  OR "if region column = APJ", then estimated onboarding date = "estimated offer date + 60 days"  (and same argument for NAM, etc.

Does anyone know the DAX to enable such a Power BI calc?

Thanks so much!

[amitchandak · Accepted solution]

@marellis , new column like example

estimated offer date

= [date hiring request approved] + 75

estimated onboarding date =

Switch( [Region],

"Europe",[estimated offer date] +90,

"APJ", [estimated offer date] +60 ,

"NAM", [estimated offer date] +30

)

[marellis]

Hi!

Thanks so much for this fast solution - it works!

Just a final question.   In some cases, the "date hiring request approved" is blank (e.g. the recruitment req is still in draft format) - but it returns a default date of "15/03/1900" in the "estimated offer date" field.  Is there DAX I could use in the estimated offer date field, to leave a blank date in this column, if there is no date yet in the "date hiring request approved" column?

Thanks