在PowerPivot中滚动12个月的总和 [英] Rolling 12 Month sum in PowerPivot

问题描述

在PowerPivot Excel 2016中，我写了一个公式，滚动12个月的销售总额如下：

In PowerPivot Excel 2016 I write a formula for rolling 12 month sum of sales as below :

Rolling Sum:=CALCULATE (
[Sales] ,
DATESBETWEEN (
    Sales[Date],
    FIRSTDATE(DATEADD(Sales[Date],-365,DAY)),
    LASTDATE (Sales[Date] )
 )
)

但似乎不正确。每个月，它只显示了那个月的销售额！
有没有人知道如何解决我的问题？

But it seems not working correctly. for each month it shows me only sales of that month! Does anybody knows how should I fix my problem?!

感谢提前

推荐答案

如果您没有日期/日历表，您不能使用时间智能功能。

If you don't have a Date/Calendar table you can't use Time Intelligence functions properly.

尽管最佳做法有一个日历/日期表，并使用时间智能功能，你可以通过使用显式过滤器获得所需的结果：

Despite the best practice would be have a Calendar/Date table and use Time Intelligence functions, you can get the desired result by using an explicit filter:

Rolling Sum :=
CALCULATE (
    [Sales],
    FILTER (
        ALL ( Sales ),
        [Date]
            >= MAX ( Sales[Date] ) - 365
            && [Date] <= MAX ( Sales[Date] )
    )
)

让我知道这是否有帮助。

Let me know if this helps.

这篇关于在PowerPivot中滚动12个月的总和的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！