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屋！