powerbi - 将日期与工作日匹配，PowerBI

问题描述

我有 2 个表，AvailabilitiesDB 和 AvailabilityTemplateDB。

AvailabilitiesDB 表看起来像

IsAvailable        StartTime                   FinishTime           Employee_ID    Week    Day Of the Week
                                                                                            
   1            25-09-2020 16:00:00      25-09-2020 19:00:00           101          39          5
   0            27-08-2020 14:00:00      27-08-2020 17:00:00           13           35          4
   1            25-11-2020 09:00:00      25-11-2020 18:00:00           66           48          3

AvailabilityTemplateDB 表如下所示：

Day Of The Week       StartTime                FinishTime           Employee_ID
   3                  18:00:00                 21:00:00                101
   2                  11:00:00                 17:00:00                13
   6                  06:00:00                 20:00:00                66

这是问题所在：我想计算员工的可用性，即他们的可用分钟数（从 StartTime 到 FinishTime）。

然而，问题是：员工都有一个模板（AvailabilityTemplateDB），这是他们的首选工作时间表，所以如果在 AvailabilitiesDB 中没有任何观察，则意味着他们使用他们的模板。但是，如果在 AvailabilitiesDB 中有针对特定员工的观察，则意味着该模板已在特定日期被覆盖。这些模板每周都会重复出现，这就是为什么它们没有具体的日期，只有“星期几”。

所以我想按照以下方式计算一些东西：计算 DatesBetween（分钟），其中员工 id = 员工 id，如果可用性数据库中有数据，它必须使用这个数字，所以第一次观察是 180 分钟，但如果那里没有日期，则必须从模板表中获取时间。我觉得这很难，因为我只有模板表中的“星期几”，而且我不知道如何将它“转换”为日期。

这是我尝试过的（不是接近最终结果），显然它不应该计算行数，我只是为了更接近答案而尝试。Date 指的是我的日期表，其中包含 AvailabiltiesDB 表中所有日期的唯一日期。

Try = 
VAR dates =
    DATESBETWEEN (
        'Date'[Date].[Date],
        MIN ( 'Date'[Date].[Date] ),
        MAX ( 'Date'[Date].[Date] )
    )
VAR Employee_ID = AvailabilitiesDB[Employee_ID]
VAR Weekdays = AvailabilitiesDB[Day Of the Week]
VAR weekdaysAndDateMatch = 
VAR COUNTZ =
    CALCULATE (
        COUNTROWS ( AvailabilitiesDB ),
        FILTER (
            AvailabilityTemplateDB,
            Weekdays = AvailabilityTemplateDB[Day of the week]
                && Employee_ID = AvailabilityTemplateDB[Employee_ID]))
RETURN IF ( COUNTZ = 0, "Yes", "No" )

我想要的结果将是 AvailabilitiesDB 表中的一列，该列以每周分钟数为单位汇总每个员工 ID 的可用性。

我希望你能进一步帮助我，提前谢谢你。

标签： powerbidax

解决方案

通过实现快照表来简化具有持续时间的事件。

处理时间的另一个好习惯是将日期部分和时间部分分成两个不同的列

所以我创建了一个包含日期、员工和时间维度的小模型

对于这个例子，我们只需要从 8 月到 11 月的月份。为了将星期几与样本数据相匹配，我必须减去 1。

Date =
ADDCOLUMNS (
    CALENDAR ( "2020-08-01", "2020-11-30" ),
    "WeekDay", WEEKDAY ( [Date] ) - 1
)

员工维度是

Employees =
ADDCOLUMNS (
    DISTINCT ( AvailabilitiesTemplateDB[Employee_ID] ),
    "Name", FORMAT ( [Employee_ID], "000" )
)

对于时间维度，我创建了一个度量作为参数，其时间粒度也可用于快照表。这可以更改以使模型适应要求。

为了保持这个例子的行数较少，我使用了 1 小时的间隔

TimeInterval = VALUE( "01:00:00" )

所以时间表变成

Time =
SELECTCOLUMNS (
    GENERATESERIES ( VALUE ( "00:00:00" ), VALUE ( "23:59:59" ), [TimeInterval] ),
    "Time", [Value]
)

快照表是每个员工可用的每个时间间隔包含一行的表。要构建此表，我们可以使用集合函数 UNION和EXCEPT。快照表只有三列：Employee_ID、Date 和 Time

此代码使用我们之前构建的日期表将日期与工作日匹配

AvailabilitiesSnapshot = 
VAR TimeGranularity = [TimeInterval]
VAR AvailableFromTemplateDB =
    SELECTCOLUMNS(
        GENERATE(
            AvailabilitiesTemplateDB,
            VAR DayOfWeek = AvailabilitiesTemplateDB[Day Of The Week]
            VAR TimesTable =
                SELECTCOLUMNS(
                    GENERATESERIES(
                        AvailabilitiesTemplateDB[StartTime],
                        AvailabilitiesTemplateDB[FinishTime] - TimeGranularity,
                        TimeGranularity
                    ),
                    "Time", [Value]
                )
            RETURN
                GENERATE(
                    FILTER( ALLNOBLANKROW( 'Date' ), 'Date'[WeekDay] = DayOfWeek ),
                    TimesTable
                )
        ),
        "Employee_ID", [Employee_ID],
        "Date", [Date],
        "Time", [Time]
    )
VAR AvailableFromDB =
    SELECTCOLUMNS(
        GENERATE(
            CALCULATETABLE( AvailabilitiesDB, AvailabilitiesDB[IsAvailable] = 1 ),
            VAR TimesTable =
                SELECTCOLUMNS(
                    GENERATESERIES(
                        AvailabilitiesDB[StartTimeOnly],
                        AvailabilitiesDB[FinishTimeOnly] - TimeGranularity,
                        TimeGranularity
                    ),
                    "Time", [Value]
                )
            RETURN
                TimesTable
        ),
        "Employee_ID", [Employee_ID],
        "Date", [Date],
        "Time", [Time]
    )
VAR UnavailableFromDB =
    SELECTCOLUMNS(
        GENERATE(
            CALCULATETABLE( AvailabilitiesDB, AvailabilitiesDB[IsAvailable] = 0 ),
            VAR TimesTable =
                SELECTCOLUMNS(
                    GENERATESERIES(
                        AvailabilitiesDB[StartTimeOnly],
                        AvailabilitiesDB[FinishTimeOnly] - TimeGranularity,
                        TimeGranularity
                    ),
                    "Time", [Value]
                )
            RETURN
                TimesTable
        ),
        "Employee_ID", [Employee_ID],
        "Date", [Date],
        "Time", [Time]
    )
RETURN
    EXCEPT(
        DISTINCT( UNION( AvailableFromTemplateDB, AvailableFromDB ) ),
        UnavailableFromDB
    )

在此代码中，我们首先添加所有可用间隔，然后删除标记为不可用的间隔，即 AvailabilitiesDB[IsAvailable] = 0

然后我们创建这个新的快照表和维度之间的关系，得到模型

使用此模型，编写一个度量来计算可用分钟数是直截了当的

AvailableTime = COUNTROWS( AvailabilitiesSnapshot ) * [TimeInterval] * 24 * 60

可以更改 TimeInterval 参数以提高详细程度。当然这会增加快照表的行数，也取决于输入表的大小。

powerbi - 将日期与工作日匹配，PowerBI

问题描述

解决方案

推荐阅读