Power BI 滚动平均 [英] Power BI Rolling Average

问题描述

我使用 Power BI 的新快速测量功能来构建 3 个月的滚动平均值计算，并且运行良好.方程如下所示.但是，当我尝试在时间序列可视化中使用此指标时，计算结果显示为当月后三个月，但我希望计算在当月停止.

I've used the new Quick Measures feature of Power BI to build a 3 month rolling average calculation and it's working well. The equation is displayed below. However, when I try to use this metric in a time series visualization, the calculations are displaying three months past the current month, but I'd like for the calculation to stop at the current month.

我已经尝试过 __DATE_PERIOD 变量但无济于事.我的页面日期过滤器设置为通过日期表上的计算列显示当前月份或 12 个月之前的所有日期.

I've played around with the __DATE_PERIOD variable to no avail. My date filter for the page is set to show all dates in the current months or 12 months prior via a calculated column on the date table.

有没有人知道如何让可视化在当月结束?

Is anyone aware of how I can get the visualization to end at the current month?

Average Days to Close Rolling Average = 
IF(
    ISFILTERED('Date'[Date]),
    ERROR("Time intelligence quick measures can only be grouped or filtered by the Power BI-provided date hierarchy."),
    VAR __LAST_DATE =
        ENDOFMONTH('Date'[Date].[Date])
    VAR __DATE_PERIOD =
        DATESBETWEEN(
            'Date'[Date].[Date],
            STARTOFMONTH(DATEADD(__LAST_DATE, -3, MONTH)),
            __LAST_DATE
        )
    RETURN
        AVERAGEX(
            CALCULATETABLE(
                SUMMARIZE(
                    VALUES('Date'),
                    'Date'[Date].[Year],
                    'Date'[Date].[QuarterNo],
                    'Date'[Date].[Quarter],
                    'Date'[Date].[MonthNo],
                    'Date'[Date].[Month]
                ),
                __DATE_PERIOD
            ),
            CALCULATE(
                'Closed Opportunities'[Average Days to Close],
                ALL('Date'[Date].[Day])
            )
        )
)

推荐答案

为了限制图表中显示的内容，您需要过滤适用的日期字段，使其仅显示您想要的日期.在这种情况下，您只希望它包含日期 <= 今天.

In order to limit what is displayed within your chart, you need to filter the applicable date field so it only displays the dates you desire. In this case, you only want it to include dates <= today.

为了在刷新时自动过滤它，我通常将自定义 DAX 列添加到我可以对其进行过滤的日期表.在这种情况下，它将类似于:

In order to automatically filter it when it is refreshed, I typically add a custom DAX column to the date table that I can filer on. In this case it would be something along the lines of:

excludeFutureDatesInd = 'Date'[Date] <= TODAY()

然后，您可以添加视觉、页面或报告过滤器，选择 [excludeFutureDatesInd] = True 的所有日期.

You can then add a visual, page, or report filter selecting all dates where [excludeFutureDatesInd] = True.

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