时间戳

首页 > 解决方案 > Moving average excluding weekends and holidays

excel - Moving average excluding weekends and holidays

问题描述

I have a table within PowerPivot currently that tracks a count of customers through our sales pipeline. From (by sales location) first interaction to charged sale. So far, I’ve creates a moving 5-day average that averages each task. Below is the DAX formula I’ve created thus far and an example table.

=
CALCULATE (
    SUM ( [Daily Count] ),
    DATESINPERIOD ( Table1[Date], LASTDATE ( Table1[Date] ), -7, DAY ),
    ALLEXCEPT ( Table1, Table1[Sales Location], Table1[Group] )
)
    / 5

enter image description here

Where I’m struggling is being able to come up with a way to exclude weekends and company observed holidays. Additionally, if a holiday falls on a weekday I would like to remove that from the average and go back an additional day (to smooth the trend).

For example, on 11/26/18 (the Monday after Thanksgiving and Black Friday) I would like to average the five business days previous (11/26/18, 11/21-11/19, and 11/16). In the example above, the moving total and average for the previous 5 days should be Intake = 41 (total) 8.2 (average), Appointment = 30 (total) 6 (average), and Sale = 13 (total) and 2.6 (average).

Based on the formula currently, each of these numbers is inaccurate. Is there an easy way to exclude these days?

Side note: I’ve created an ancillary table with all holidays that is related to the sales data that I have.

Thank you for the help!

标签： excelpowerbidaxpowerpivot

解决方案

为此，我建议使用与Table1该Date列相关的日历表，该表也有一个列IsWorkday，1如果那一天是工作日，0否则。

设置完成后，您可以编写如下度量：

Moving Avg =
VAR Last5Workdays =
    SELECTCOLUMNS (
        TOPN (
            5,
            FILTER (
                DateTable,
                DateTable[Date] <= EARLIER ( Table1[Date] )
                    && DateTable[IsWorkday] = 1
            ),
            DateTable[Date], DESC
        ),
        "Workday", DateTable[Date]
    )
RETURN
    CALCULATE (
        SUM ( Table1[Daily Count] ),
        Table1[Date] IN Last5Workdays
        ALLEXCEPT ( Table1, Table1[Sales Location], Table1[Group] ),
    )
        / 5

此处的TOPN函数返回前 5 行，DateTable其中每行必须是小于或等于当前Table1行中日期的工作日（该EARLIER函数引用定义当前行的较早行上下文）。

然后，我SELECTCOLUMNS通过选择单个列（我已将其命名为Workday）来将此表转换为列表。从那里开始，基本上你的日期过滤器改变了一点。

推荐阅读