Power Bi dax的平均滚动月份 [英] Rolling month average in power bi dax

问题描述

我有这两列：
SALES_CALLSID
QFQ4TA5006C2
QZPIOA18LW8A
QS4GSA300PU0
....;和
MEETING_DATE
2014年7月10日12:00
2015/3/27 12:00
2015/1/3/12：00

I have these two columns: SALES_CALLSID QFQ4TA5006C2 QZPIOA18LW8A QS4GSA300PU0 ....; and MEETING_DATE 7/10/2014 12:00 3/27/2015 12:00 11/3/2015 12:00

我需要第一列的数量（salesid），但是该数量应该是每个月的过去十二个月的平均值，例如，对于7月-7月的销售id的计数+最近11的计数几个月（即基于会议日期列），同样必须每隔一个月进行一次度量。

I need the count of the first column (salesid) but this count should be a trailing twelve month average for every month, i.e. for example for July- the count of sales id of july + count of last 11 months (i.e. based on the meeting date column), similarly this measure has to be made for every other month.

销售呼叫ID列是一个文本字段。我可以使用公式COUNTA（AccountSalesCalls [SALES_CALLSID]）`查看计数。

The "Sales Call ID" column is a text field. I can see the count using the formula COUNTA(AccountSalesCalls[SALES_CALLSID])`.

我用来获取最近12个月的销售ID总数的公式如下：

The formula i built to get the total count of sales id for last 12 months is as follows:

CALCULATE (
    COUNTA ( AccountSalesCalls[SALES_CALLSID] ),
    DATESBETWEEN (
        AccountSalesCalls[MEETING_DATE],
        NEXTDAY ( SAMEPERIODLASTYEAR ( LASTDATE ( AccountSalesCalls[MEETING_DATE] ) ) ),
        LASTDATE ( AccountSalesCalls[MEETING_DATE] )
    )
)

执行此操作时出现错误：

When i do this i get an error:

在$的调用中指定了包含重复日期的日期列b $ b函数 DATESBETWEEN。

A date column containing duplicate dates was specified in the call to function 'DATESBETWEEN'. This is not supported.

然后我建立了一个DATE表，并用DATE表中的列替换了MEETING_DATE列，如下所示：

I then built a DATE table and replaced MEETING_DATE column with column in DATE table as follows:

CALCULATE (
    COUNTA ( AccountSalesCalls[SALES_CALLSID] ),
    DATESBETWEEN (
        'Date'[Date],
        NEXTDAY ( SAMEPERIODLASTYEAR ( LASTDATE ( AccountSalesCalls[MEETING_DATE] ) ) ),
        LASTDATE ( AccountSalesCalls[MEETING_DATE] )
    )
)

这只是给我该月的计数，与 COUNTA（AccountSalesCalls [SALES_CALLSID]）。

This just gives me the count of that month which is the same result as COUNTA(AccountSalesCalls[SALES_CALLSID]).

我仍然没有完成除法部分来获取平均值，但首先想弄清楚如何使总计数有效。任何帮助将不胜感激。

I still haven't done the division part to get the average but first wanted to figure out how to make the total count work. Any help would be appreciated.

推荐答案

而不是除以12，我已经看到Rob Collie用

Instead of dividing by 12, I have seen Rob Collie handle the division by

COUNTROWS（VALUES（'Date'[Year-Month]））

COUNTROWS ( VALUES ( 'Date'[Year-Month] ) )

其中[Year-Month]是一个计算列在日期表中：

where [Year-Month] is a calculated column in the 'Date' table:

=格式（日期 [Date]， YYYY-mm）

= FORMAT ( 'Date'[Date], "YYYY-mm" )

以这种方式使用VALUES还可以处理可用数据少于12个月的情况。

Using VALUES in this way also handles cases where the available data covers less than 12 months.

我还看到Chandoo使用了类似的方法，但是使用了COUNTROWS检查是否有十二个月可作为滚动平均值的基础（如果没有，则返回空白）。

I have also seen Chandoo use a similar approach, but using the COUNTROWS to check if there are twelve months available to base the rolling average on (and if not, return blank).

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