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

问题描述

我有这两列:SALES_CALLSIDQFQ4TA5006C2QZPIOA18LW8AQS4GSA300PU0.....;和MEETING_DATE2014 年 7 月 10 日 12:002015 年 3 月 27 日 12:002015 年 11 月 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) 的计数，但这个计数应该是每个月过去 12 个月的平均值，例如 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:

在调用中指定了包含重复日期的日期列函数日期".这不受支持.

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

然后我构建了一个 DATE 表，并将 MEETING_DATE 列替换为 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.

推荐答案

我见过 Rob Collie 处理除以 12，而不是除以

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:

= FORMAT ('日期'[日期], "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屋！