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
lasmithfla
Helper I
Helper I

Business days based on dynamic fields

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.
1 ACCEPTED SOLUTION
v-alq-msft
Community Support
Community Support

Hi, @lasmithfla 

 

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

Table:

f1.png

 

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:

f3.png

 

 

Best Regards

Allan

 

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

 

 

View solution in original post

3 REPLIES 3
v-alq-msft
Community Support
Community Support

Hi, @lasmithfla 

 

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

Table:

f1.png

 

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:

f3.png

 

 

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.

amitchandak
Super User
Super User

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.