Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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).
Solved! Go to 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.
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.
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.
@lasmithfla , Refer my HR Blog and The date diff file. Check for working day formula
Working day file -https://www.dropbox.com/s/y47ah38sr157l7t/Order_delivery_date_diff.pbix?dl=0
User | Count |
---|---|
124 | |
108 | |
99 | |
62 | |
62 |
User | Count |
---|---|
137 | |
116 | |
102 | |
71 | |
61 |