Skip to main content

    Power BI

    New

    NETWORKDAYS DAX /Power Query formula

    Vote (87) Share
    Marian Szetyinszki's profile image

    Marian Szetyinszki on 09 Apr 2021 11:19:56

    Enable an engine built in formula to calculate Excel equivalent of networking days: Number of business days between 2 dates, excluding weekends and bank holidays provided as an optional list of dates.
    Currently only complex solutions using other DAX / Query formulas can be used , this solution is very slow and memory intense when applied across many rows and Country holidays table in a multi country regions .

    Comments (23)
    Marian Szetyinszki's profile image Profile Picture

    on 22 Jun 2021 23:06:06

    RE: NETWORKDAYS DAX /Power Query formula

    The NETWORKDAYS function in Excel must be one of the most commonly used. This needs to be implemented in BI as it is a fundamental point for analysis for any corporation.

    Marian Szetyinszki's profile image Profile Picture

    on 22 Jun 2021 23:05:59

    RE: NETWORKDAYS DAX /Power Query formula

    Networkdays is a good function in excel which is omitted in PowerBI.
    Even better have it work to a fraction of a day as a decimal ie a result of 2 as returned in Excel should return as 2.5 for example for 2 and a half days.

    Marian Szetyinszki's profile image Profile Picture

    on 22 Jun 2021 23:05:47

    RE: NETWORKDAYS DAX /Power Query formula

    Can we have a function similar to Networkdays in excel to determine the net working days between 2 dates. for the list of holiday which will be optional, we can enable it via list or separate table.

    Marian Szetyinszki's profile image Profile Picture

    Andy-JB Chen on 13 Apr 2021 02:13:24

    RE: NETWORKDAYS DAX /Power Query formula

    Sorry, does NOT work in calculated column

    Marian Szetyinszki's profile image Profile Picture

    Andy-JB Chen on 13 Apr 2021 02:12:44

    RE: NETWORKDAYS DAX /Power Query formula

    The proposed method does work in calculated column. And should consider it as fundamental functions. Thanks!

    Marian Szetyinszki's profile image Profile Picture

    Nishant Burdhan on 06 Jul 2020 00:13:37

    RE: NETWORKDAYS DAX /Power Query formula

    But it doesnot work if one of the date is blank or nulldate. is there anyway we can calculate the days between two dates column when some of the value is blank.

    Marian Szetyinszki's profile image Profile Picture

    Power BI User on 06 Jul 2020 00:10:52

    RE: NETWORKDAYS DAX /Power Query formula

    I agree after LOTS os searching this post helped me to get a function which is similar.

    https://community.powerbi.com/t5/Desktop/Network-days-with-decimals/td-p/405476

    Marian Szetyinszki's profile image Profile Picture

    Cody Martelli on 05 Jul 2020 23:52:29

    RE: NETWORKDAYS DAX /Power Query formula

    It's not a hard issue to work around, but something that shouldn't require a work around. All current solutions rely on non-standard holiday table locations. If there was a Microsoft provided standard solution, there'd be a universal method we'd be able to use to reproduce this fairly basic functionality. There's data still living in Excel that I'm reluctant to move for this specific reason.

    Marian Szetyinszki's profile image Profile Picture

    Gregory J. Deckler on 05 Jul 2020 23:14:47

    RE: NETWORKDAYS DAX /Power Query formula

    I added this to the Quick Measure Gallery here:
    https://community.powerbi.com/t5/Quick-Measures-Gallery/Net-Work-Days/m-p/367362

    Marian Szetyinszki's profile image Profile Picture

    Power BI User on 05 Jul 2020 23:10:23

    RE: NETWORKDAYS DAX /Power Query formula

    Aside from being really useful (so should have been added by now?), why are there functions in Excel that are not in Power BI?

    Merged Idea (4)