Forum Discussion

yaya1974's avatar
yaya1974
Helper III
2 years ago
Solved

Work Days in Month

Hi, I need a calucation to give me the work day of each month.

Do you have a formula that calculates business days, excluding weekends and holidays?

So, like this year 1/2/24 is working day 1. But I am looking for it to start over each month.

All the examples I have found are total working days for the year. I need for the month. And not total, meaning not 22 days in Jan. I need 1 – 22 detailed out for each month.

Jan 2 = 1

Jan 3 = 2

Jan 4 = 3, 

Feb 1 = 1 , etc.

 

This is what I have right now, but is reserved.   

= NETWORKDAYS('Date'[Start Date],'Date'[End Date])   

This is results:

Jan 1 = 23

Jan 2 - 22, etc.

 

Any help ?

Thank you!

Need Help
  • lbendlin's avatar
    lbendlin
    2 years ago

    Don't waste your time .  Use an external table.  It's not necessary to calculate this over and over again.

  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi yaya1974 ,

    First use this DAX to add a new column:

    Month = MONTH('Table'[Date])

    Then please try this DAX to create a new column:

    NETWORKDATS = 
VAR MIN_DAY = 
CALCULATE(
    MIN('Table'[Date]),
    FILTER(
        'Table',
        'Table'[Month] = EARLIER('Table'[Month])
    )
)
VAR MAX_DAY = 
CALCULATE(
    MAX('Table'[Date]),
    FILTER(
        'Table',
        'Table'[Date] <= EARLIER('Table'[Date]) && 'Table'[Month] = EARLIER('Table'[Month])
    )
)
RETURN
NETWORKDAYS(MIN_DAY, MAX_DAY)

    The final output is as below:


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

2 Replies

  • lbendlin's avatar
    lbendlin
    Super User
    2 years ago

    Don't waste your time .  Use an external table.  It's not necessary to calculate this over and over again.

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi yaya1974 ,

    First use this DAX to add a new column:

    Month = MONTH('Table'[Date])

    Then please try this DAX to create a new column:

    NETWORKDATS = 
VAR MIN_DAY = 
CALCULATE(
    MIN('Table'[Date]),
    FILTER(
        'Table',
        'Table'[Month] = EARLIER('Table'[Month])
    )
)
VAR MAX_DAY = 
CALCULATE(
    MAX('Table'[Date]),
    FILTER(
        'Table',
        'Table'[Date] <= EARLIER('Table'[Date]) && 'Table'[Month] = EARLIER('Table'[Month])
    )
)
RETURN
NETWORKDAYS(MIN_DAY, MAX_DAY)

    The final output is as below:


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