Business days based on dynamic fields

[lasmithfla]

I'm struggling with this.  I have a slicer that I'm using as dynamic dates for starting period and ending period.

I have a calendar table that I can count the business days, I'm able to count the bus. days using the starting and ending periods BUT when someone is hired within that time period, their bus days will be less.  So for example, anyone hired prior to my starting period would have a count of business days of 245.   That calculation needs to be based on the difference of the Hire date and the ending period.

Example  - my starting and ending dates are 3/1/19 to 2/28/20 (245 bus days, taking out holidays too)

Emp ID         Hire date        No days

1                    2/1/18              245

2                    2/1/20               20

3                    5/15/97            245

The person with the 20 days - Employee No 2 is what I can't figure out.

My field in the table is hire date, and there are thousands of people so the dates are not unique in the table.

This is my calc for the workdays for everyone hired prior to my start date.  The calc. below works for the dynamic date range that would be selected (in this case 3/1/19 to 2/28/20).   

CountWorkdays = VAR FirstDay = CALCULATE(Min('Calendar Table'[Date]),ALLSELECTED('Calendar Table'[Date]))

VAR LASTDAY = CALCULATE(MAX('Calendar Table'[Date]),ALLSELECTED('Calendar Table'[Date]))

RETURN CALCULATE(SUM('Calendar Table'[IsWorkingDay]),DATESBETWEEN('Calendar Table'[Date],FirstDay,LASTDAY))

I need another calc for the people hired within the time period. 

Relatively new to powerbi so this is all new to me.

[v-alq-msft · Accepted solution]

Hi, @lasmithfla 

Based on your description, I created data to reproduce your scenario. My business days only exclude weekends.

Table:

Calendar:

Calendar = CALENDARAUTO()

You may create a calculated column and a measure as below.

Calculated column:
IsWorkingDay = IF(WEEKDAY('Calendar'[Date]) in {1,2,3,4,5},1,0)

Measure:
CountWorkdays =
var _hiredate = SELECTEDVALUE('Table'[Hire date])
var firstday =
CALCULATE(
MIN('Calendar'[Date]),
ALLSELECTED('Calendar')
)
var lastday =
CALCULATE(
MAX('Calendar'[Date]),
ALLSELECTED('Calendar')
)
var _re1 =
CALCULATE(
SUM('Calendar'[IsWorkingDay]),
DATESBETWEEN(
'Calendar'[Date],
firstday,
lastday
)
)
return
IF(
_hiredate<firstday,
_re1,
IF(
_hiredate>=firstday&&
_hiredate<=lastday,
CALCULATE(
SUM('Calendar'[IsWorkingDay]),
DATESBETWEEN(
'Calendar'[Date],
_hiredate,
lastday
)
)
)
)

Result:

Best Regards

Allan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[lasmithfla]

Thank you so much, worked perfect!  I had something that was broke out in several measures, having one that works is so much more effecient.  Thank you for your time.

[amitchandak]

@lasmithfla , Refer my HR Blog and The date diff file. Check for working day formula

Check - https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

Working day file -https://www.dropbox.com/s/y47ah38sr157l7t/Order_delivery_date_diff.pbix?dl=0