Forum Discussion

lasmithfla's avatar
lasmithfla
Icon for Helper I rankHelper I
6 years ago
Solved

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.
Need Help
  • v-alq-msft's avatar
    v-alq-msft
    6 years ago

    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.

     

     

3 Replies

  • v-alq-msft's avatar
    v-alq-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    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's avatar
      lasmithfla
      Icon for Helper I rankHelper I
      6 years ago

      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.