Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

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 ...
  • Anonymous's avatar
    Anonymous
    5 years ago

    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])
Anonymous's avatar
Anonymous
Not applicable
5 years ago

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