Calculating average NETWORK days Power BI

Question

I've been reading various threads and guides to performing a 'NETWORKDAYS' style calculation in Power BI but struggling to make it work.

I have a table like this:

Team |  Meeting    |  Report
aaa  | 1/1/2018    |  9/1/2018
aaa  | 1/1/2018    |  7/1/2018
bbb  | 1/1/2018    |  1/2/2018
bbb  | 1/1/2018    | 
ccc  | 1/1/2018    |  3/3/2018
aaa  | 1/1/2018    |

And I want to return the average days without weekends and holidays, something like this:

Team | average
aaa  | 5 (10/2)
bbb  | 23 (45/1)
ccc  | 45 (45/1)

I have this function, which seems to work albeit clunkily, but I don't know how to remove the non-weekdays from the Date table:

AVERAGEX(FILTER(Planning,NOT(ISBLANK(Planning Actual_FinalReport]))),
    (COUNTROWS(DATESBETWEEN(DateTable[Date],
    Planning[Actual_ClosingMeeting],Planning [Actual_FinalReport]))
))

Where DateTable is:

Date    | Weekday
5/1/2018| 1
6/1/2018| 0
7/1/2018| 0

and so on...

Essentially, I want to iterate over Planning (filtering out blanks in [Report]) and count the dates between Meeting and Report from the Dates table, filtered by Weekday = 1. It's the syntax to link the tables I think I'm struggling with.

Alexis Olson Alexis Olson · Accepted Answer · 2018-03-16T20:27:47

Modifying your formula a bit, how about something like this?

Average = AVERAGEX(
              FILTER(Planning,
                  NOT(ISBLANK(Planning[Actual_FinalReport]))),
              COUNTROWS(
                  FILTER(DateTable,
                      DateTable[Date] IN DATESBETWEEN(
                                             DateTable[Date],
                                             Planning[Actual_ClosingMeeting],
                                             Planning[Actual_FinalReport]) &&
                      DateTable[Weekday] = 1)))

I'm adding the Weekday filter at the end.

Calculating average NETWORK days Power BI

1 Answers