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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.