Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
prashantg364
Helper II
Helper II

DAX Query for working days

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.

ExampleStart dateEnd DateWorking days using "NETWROKDAYS DAX"The result I am looking for
A01-Jan-2431-Jan-242320
B01-Jan-2415-Jan-241111
C01-Jan-2415-Feb-243420+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

 

3 REPLIES 3
philadams
Frequent Visitor

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.

123abc
Community Champion
Community Champion

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.

Thanks for the suggestion, but still the result is incorrect

prashantg364_1-1708068283604.png

 

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Feb2025 NL Carousel

Fabric Community Update - February 2025

Find out what's new and trending in the Fabric community.