topic Re: DAX Query for working days in DAX Commands and Tips

DAX Query for working days

prashantg364 — Fri, 16 Feb 2024 06:42:36 GMT

Hi All,

I have a date table as shown in the table below.

I want to calculate the total number of working days based on the date filtered by the user in visualization and restrict the maximum working days for a month to 20 days.

Below are a few examples of the results that I am looking for.

Example	Start date	End Date	Working days using "NETWROKDAYS DAX"	The result I am looking for
A	01-Jan-24	31-Jan-24	23	20
B	01-Jan-24	15-Jan-24	11	11
C	01-Jan-24	15-Feb-24	34	20+11=31

Date

01-Jan-24

02-Jan-24

03-Jan-24

04-Jan-24

05-Jan-24

06-Jan-24

25-Mar-24

26-Mar-24

27-Mar-24

28-Mar-24

29-Mar-24

30-Mar-24

31-Mar-24

Re: DAX Query for working days

123abc — Fri, 16 Feb 2024 06:46:50 GMT

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:

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

Here's how you can do it:

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
)

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.

Re: DAX Query for working days

prashantg364 — Fri, 16 Feb 2024 07:24:53 GMT

Thanks for the suggestion, but still the result is incorrect

Re: DAX Query for working days

philadams — Fri, 16 Feb 2024 08:46:59 GMT

Do you have the option of amending the source for the date table? I implemented this in MySQL using the following in the view -

NOT( WEEKDAY(dat.date) IN (5,6) OR hols.date IS NOT NULL ) AS 'Is Working Day',

'hols' is just a table containing dates for any holidays the buiness will be shut for. This way you can then simply sum this field for any filtered range on your date table.