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 ...
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.

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.