Power BI is turning 10, and we’re marking the occasion with a special community challenge. Use your creativity to tell a story, uncover trends, or highlight something unexpected.
Get startedJoin us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.
I'm trying to add a measure to calculate the number of working days between two dates, but the measure I've built adds an extra day. 0 days shows as 1 working days, 1 day as 2 working days and so on. The data is in Direct Query and I have no access to Power Query unfortunately.
Here's the DAX for the measures (days and working days):
DEFINE
MEASURE 'Stage2'[form response days] = CALCULATE(
DATEDIFF(
MAX('Stage2'[Form Received Date]),
MAX(KPI_DATA[Stage 2 Start Date]),
DAY
),
USERELATIONSHIP(
'Calendar'[Date],
'KPI_DATA'[Stage 2 Start Date]
)
)
MEASURE 'Stage2'[form response working days]=
CALCULATE(
NETWORKDAYS(MAX('Stage2'[Form Received Date]),MAX('KPI_DATA'[Stage 2 Start Date])),
USERELATIONSHIP('Calendar'[Date],'KPI_DATA'[Stage 2 Start Date])
)
EVALUATE
SUMMARIZECOLUMNS(
'Stage2'[id],
'Stage2'[Form Received Date],
KPI_DATA[Stage 2 Start Date],
"form response days", 'Stage2'[CF response days],
"form response working days", 'Stage2'[CF response working days]
)
And here's the relevant section of the model, in case that helps:
Thanks!
Solved! Go to Solution.
Stumbled upon this yesterday, too. The documentation says "Returns the number of whole workdays between two dates (inclusive)." So "inclusive" means including the start date. My solutution was to simply subtract one day.
Stumbled upon this yesterday, too. The documentation says "Returns the number of whole workdays between two dates (inclusive)." So "inclusive" means including the start date. My solutution was to simply subtract one day.
Ah, I did try that but it didn't seem to work with my model. (And didn't seem to fix the issue either).
this did work for mine. Maybe it is where you need to subtract one day is in a different location. I didn't literally write -1 to take out the day. If I needed +3 days, I just wrote +4 days, and doing that adjusted for it counting today in the formula.
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
10 | |
9 | |
8 | |
8 | |
7 |
User | Count |
---|---|
14 | |
12 | |
11 | |
10 | |
8 |