Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
jasontran
Helper I
Helper I

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

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

TTO.PNG

 

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.
 
 
0 REPLIES 0

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors