Time to Offer/Hire (exc weekends and minus frozen days)

[jasontran]

For those who work in HR/Recruitment. I've been working on building Time to Offer (essentially a DateDiff with a couple conditions)

For clarity heres what I've been able to do thus far.

//indexid is a unique identifier associated to a position

Time to Offer = 

VAR A = [Requisition Approval Date]

VAR B = [Offer Initiated]

VAR C = CALCULATE (DATEDIFF(A,B,DAY),[indexid] = EARLIER[indexid]))

RETURN IF(ISBLANK([Offer Initiated], BLANK(),C)

Time to Offer - WE = 

//Time to Offer - Weekends

VAR A = DATEDIFF([Requisition Approval Date],[Offer Initiated],DAY) -

CALCULATE(

COUNTROWS('calendar'),

'calendar'[Weekend]=1,

DATESBETWEEN([Date],[Requisition Approval Date],[Offer Initiated]))

RETURN IF(ISBLANK([Offer Initiated]),BLANK(),A)

// using https://poszytek.eu/en/microsoft-en/office-365-en/powerbi-en/powerbi-datediff-only-working-days-hour...

Days Frozen =

VAR A = [Freeze 1]

VAR B = [Unfreeze 1]

VAR C = [Freeze 2]

VAR D = [Unfreeze 2]

VAR E = [Freeze 3]

VAR F = [Unfreeze 3]

VAR G = [Freeze 4]

VAR H = [Unfreeze 4]

VAR I = [Freeze 5]

VAR J = [Unfreeze 5]

VAR AB = -(CALCULATE( DATEDIFF(B,A,DAY),

[indexid]=EARLIER([indexid)))

VAR CD = -(CALCULATE( DATEDIFF(D,C,DAY),

[indexid]=EARLIER([indexid])))

VAR EF = -(CALCULATE( DATEDIFF(F,E,DAY),

[indexid]=EARLIER([indexid])))

VAR GH = -(CALCULATE( DATEDIFF(G,H,DAY),

[indexid]=EARLIER([indexid])))

VAR IJ = -(CALCULATE( DATEDIFF(J,I,DAY),

[indexid]=EARLIER([indexid])))

VAR Total = AB+CD+EF+GH+IJ

RETURN Total

Time To Offer - Freeze =

VAR A = [Requisition Approval Date]

VAR B = [Offer Initiated]

VAR C = CALCULATE(

DATEDIFF(A,B,DAY),

[indexid]=([indexid]))- RELATED([Days Frozen])

RETURN

IF(ISBLANK([Offer Initiated]),BLANK(),C)

There are occurences when the freeze dates occur post the offer date and results in negative figures.

Any recommendations on how I can structure the DAX formula to factor the various conditions above.

Order of logic would be:

DATEDIFF([Requisition Approval Date],[Offer Initiated],DAY) - Weekends

Then WHERE Freeze Dates fall within the DATEDIFF deduct from the total. 

ELSE return the original DATEDIFF.