Reply
MJDS
Frequent Visitor

Networkdays function is adding an extra day

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])

    )
  Here's the result I get when I run it in DAX Query View:
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]

    )
MJDS_0-1726149754950.png

And here's the relevant section of the model, in case that helps:

MJDS_1-1726149811184.png

Thanks!

1 ACCEPTED SOLUTION
sjoerdvn
Super User
Super User

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.

View solution in original post

2 REPLIES 2
sjoerdvn
Super User
Super User

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.

MJDS
Frequent Visitor

Ah, I did try that but it didn't seem to work with my model.  (And didn't seem to fix the issue either).

avatar user

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!

Jan25PBI_Carousel

Power BI Monthly Update - January 2025

Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.

Jan NL Carousel

Fabric Community Update - January 2025

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

Top Solution Authors (Last Month)
Top Kudoed Authors (Last Month)