DAX Query for working days

To calculate the total number of working days based on the user-selected date range and restrict the maximum working days for a month to 20 days in Power BI using DAX, you can follow these steps:

1. Create a measure that calculates the total working days using the NETWORKDAYS function.
2. Create another measure to restrict the maximum working days to 20 days per month.

Here's how you can do it:

1. Calculate Total Working Days: Create a measure named Total Working Days that calculates the total working days between the start date and end date selected by the user.

Total Working Days =
VAR StartDate = MIN('Date'[Date])
VAR EndDate = MAX('Date'[Date])
RETURN
NETWORKDAYS(StartDate, EndDate)

Restrict Maximum Working Days to 20: Create a measure named Restricted Working Days that restricts the total working days to a maximum of 20 days per month.

Restricted Working Days =
VAR TotalDays = [Total Working Days]
VAR MaxDaysPerMonth = 20
VAR StartMonth = MONTH(MIN('Date'[Date]))
VAR EndMonth = MONTH(MAX('Date'[Date]))
VAR MonthsInRange = EndMonth - StartMonth + 1
RETURN
IF(
TotalDays > MaxDaysPerMonth * MonthsInRange,
MaxDaysPerMonth * MonthsInRange,
TotalDays
)

1. Display the Results: Now, you can use the Restricted Working Days measure in your visualizations to display the desired result.

Ensure that you have a proper date table ('Date') that covers the entire range of dates in your dataset.

With these measures in place, you can now calculate the total working days based on the user-selected date range and restrict the maximum working days to 20 days per month, as per your requirements.

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

In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.