计算平均NETWORK天数Power BI [英] Calculating average NETWORK days Power BI

问题描述

我一直在阅读各种线程，并指导如何在Power BI中执行 NETWORKDAYS样式的计算，但仍在努力使其工作。

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

DateTable在哪里：

Where DateTable is:

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

依此类推...

本质上，我想遍历Planning（在[Report]中过滤掉空白），并从Dates表中计算Meeting和Report之间的日期，并按Weekday = 1进行过滤。这是链接我认为很费力的表的语法

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.

推荐答案

稍微修改一下公式，这样的事情怎么样？

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.

这篇关于计算平均NETWORK天数Power BI的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！