Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe 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.
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 |
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.
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:
Here's how you can do it:
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
)
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
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
If you love stickers, then you will definitely want to check out our Community Sticker Challenge!
User | Count |
---|---|
24 | |
17 | |
12 | |
12 | |
10 |
User | Count |
---|---|
32 | |
25 | |
16 | |
14 | |
13 |