How do I get working days between dates in Power BI?

Question

Currently, I have calculated the working days between two date by using the following formula.

TAT = CALCULATE(SUM('Days Table'[Is Work Day]),
DATESBETWEEN('Days Table'[Date],'Re-run Data'[DATE_ORDERED],'Re-run 
Data'[DATE_COMPLETED]))

The problem is that if a client level has multiple orders that span all the working days in the Days Table, their sum includes all of the days instead of the sum of days between orders. This also skews the averages.

Does anyone have simpler solution to get number of working days between two dates?

can you please provide a better shot and what your desired output would be please? — StelioK
The data in your question is incomplete. Do you have a holidays table you are referring to, to calculate the [Is Work Day] column? — Olly

Olly Olly · Accepted Answer · 2019-02-18T12:49:36

Your current solution relies on having the [Is Work Day] column in your calendar table.

This measure returns the number of working days between the Date Ordered and Date Completed - based on working days being Monday - Friday, and excluding dates listed in a 'Holidays' table, without needing any calculated columns:

TAT = 
    COUNTROWS ( 
        FILTER ( 
            ADDCOLUMNS(
                DATESBETWEEN ( 
                    'Calendar'[Date],
                    MIN ( 'Re-run Data'[DATE_ORDERED] ),
                    MAX ( 'Re-run Data'[DATE_COMPLETED] )
                ),
                "Is Weekday", WEEKDAY ( 'Calendar'[Date], 2) < 6,
                "Is Holiday", CONTAINS ( Holidays, Holidays[Holiday Dates], 'Calendar'[Date] )
            ),
            [Is Weekday] = TRUE() && [Is Holiday] = FALSE()
        )
    )

See https://pwrbi.com/so_54718437/ for worked example (PBIX)

How do I get working days between dates in Power BI?

2 Answers