Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Excel NETWORKDAYS function in PowerBI

Hi all,

 

I'm trying to pro-rate weekly costs by multiplying the monthly cost by the number of business days that have occured the previos week. For example, I would pull data today (11/10/20) and publish a weekly report that covers data up to the Friday (11/6/2020) of the previous week. For reporting week 11/6/20, there will have been 5 business days in November that have passed; 10 business days on reporting week 11/13, etc; total of 21 business days in the month of November. So the first week will have Monthly Cost * (5/21), the second week (10/21) and so on. 

 

1. How do I get PBI to count the total number of business days per month (21 for November), (23 in December), etc?

2. How do I get PBI to count how many business days have passed in the month since the last report date each week?

 

FYI I've looked through a few related posts on duplicating the "NETWORK" function from Excel, but I'm receiving errors in my dataset that I can't have dates in the future (ie: 12/1/2020) in my dataset. 

 

Here is what a portion of my dataset looks like. There are 50 other "Projects" with 12 "Monthly Forecast Costs". 

 

birchr_2-1605021096179.png

 

Any help would be appreciated! Thank you!

1 ACCEPTED SOLUTION
Anonymous
Not applicable

For those wondering what I ended up doing.

 

I used Greg's formula below, but I removed all future months from my data set. So I'll have to manually unfilter each month as we get to it. That seems to be the only way to bypass the error of "can't have future days in my dataset."

 

NetWorkDaysCurrentWeek = 
VAR Calendar1 = CALENDAR(MAX('Forecast - Grid Enhancement'[Date]),MAX('Forecast - Grid Enhancement'[Reporting Week]))
//VAR Holidays = DATATABLE("Date",DATETIME,{{}})
VAR Holidays1 = DATATABLE("Date",DATETIME,
    {
        {"11/26/2020 12:00:00 AM"},
        {"11/27/2020 12:00:00 AM"},
        {"12/24/2020 12:00:00 AM"},
        {"12/25/2020 12:00:00 AM"}
    })
VAR Calendar2 = EXCEPT(Calendar1,Holidays1)
VAR Calendar3 = ADDCOLUMNS(Calendar2,"WeekDay",WEEKDAY([Date],2))
RETURN COUNTX(FILTER(Calendar3,[WeekDay]<6),[Date])

View solution in original post

3 REPLIES 3
Anonymous
Not applicable

For those wondering what I ended up doing.

 

I used Greg's formula below, but I removed all future months from my data set. So I'll have to manually unfilter each month as we get to it. That seems to be the only way to bypass the error of "can't have future days in my dataset."

 

NetWorkDaysCurrentWeek = 
VAR Calendar1 = CALENDAR(MAX('Forecast - Grid Enhancement'[Date]),MAX('Forecast - Grid Enhancement'[Reporting Week]))
//VAR Holidays = DATATABLE("Date",DATETIME,{{}})
VAR Holidays1 = DATATABLE("Date",DATETIME,
    {
        {"11/26/2020 12:00:00 AM"},
        {"11/27/2020 12:00:00 AM"},
        {"12/24/2020 12:00:00 AM"},
        {"12/25/2020 12:00:00 AM"}
    })
VAR Calendar2 = EXCEPT(Calendar1,Holidays1)
VAR Calendar3 = ADDCOLUMNS(Calendar2,"WeekDay",WEEKDAY([Date],2))
RETURN COUNTX(FILTER(Calendar3,[WeekDay]<6),[Date])
Greg_Deckler
Super User
Super User

@Anonymous Have you read through this? https://community.powerbi.com/t5/Quick-Measures-Gallery/Net-Work-Days/m-p/367362#M109. Also, Chapter 2, Recipe 10 of my book DAX Cookbook has an improved version of this. https://github.com/gdeckler/DAXCookbook. You can download the PBIX from there.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

Hey @Greg_Deckler ,

 

Thanks for your feedback. I created 2 measures based on your examples...

 

NetWorkDays1 = 
    VAR __Date1 = MAX('Forecast - Grid Enhancement'[Todays Date])
    VAR __Date2 = MAX('Baseline Forecast'[Date])
    VAR __Date1a = MINX( { __Date1, __Date2 },[Value])
    VAR __Date2a = MAXX( { __Date1, __Date2 },[Value])
    VAR __Calendar = 
        ADDCOLUMNS(
            CALENDAR(__Date1a, __Date2a),
            "__WeekDay",
            WEEKDAY([Date],2)
        )
RETURN
    COUNTX(
        FILTER(
            __Calendar,
            [__WeekDay] < 6
        ),
        [Date]
    )
NetworkDays2 = 
VAR Calendar1 = CALENDAR(MAX('Baseline Forecast'[Date]),MAX('Forecast - Grid Enhancement'[Reporting Week]))
VAR Calendar2 = ADDCOLUMNS(Calendar1,"WeekDay",WEEKDAY([Date],2))
RETURN COUNTX(FILTER(Calendar2,[WeekDay]<6),[Date])

 

 

Here is what the tables display. Am I doing something wrong?

 

birchr_0-1605026905877.png

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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

Top Solution Authors