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)
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.