powerpivot - 在 Powerpivot 中计算时间段时测量为日期变量

Question

我的情况如下:

这是在 Excel 2013(32 位)中开发的 Powerpivot 解决方案。我有一个交易表，其中包含交易金额、类别和过帐日期。我想做的是根据时间范围提出一些不同的计算。

• 当日进口金额总和(所有可提供最新过帐日期的交易)。
• 当月累计金额(最近一笔交易的当月)
• 上月同期金额总和(月初至今减去一个月)
• 上月总金额(上月总金额)

因此，我们的想法是创建一个“当天”度量作为所有其他度量的立足点。

[Current day] = LASTDATE('TransactionTable'[Posting Date])

在总结之前，我想创建代表每个时期的开始和结束日期的度量(以显示在报告中并进行更简单的度量)，这就是我遇到麻烦的地方。

[First day of current month] = STARTOFMONTH([Current Day])

给我错误:“在用作表过滤器表达式的 True/False 表达式中使用了函数‘CALCULATE’。这是不允许的。”

有了这个，我想为本月做这样的事情:

[Sum of amount current month] = CALCULATE(SUM('Transactiontable'[Amount]);DATESBETWEEN('DateTable'[Date]; [First day of current month];[Current day]))

这是上个月的总数:

[First day of previous month] = DATEADD([First day of current month];-1;MONTH)
[Last day of previous month] = EOMONTH([Current day];-1)

CALCULATE(SUM('Transactiontable'[Amount]);DATESBETWEEN('DateTable'[Date]; [First day of previous month]; [Last day of previous month]))

感觉我没有以“正确”的方式使用这些措施...基本上我想创建动态措施，根据交易表中的最新发布日期来更改时间范围。这是要走的路吗？

谢谢大家，

Answer 1

首先你需要使用ALL()

您非常接近 - 使 [CurrentDate] 正确的技巧是使用 ALL() 函数。这个方便的功能会覆盖您拥有的任何过滤条件。

那么让我们看看使用 LASTDATE('TransactionTable'[Posting Date]) 的 [CurrentDate] 度量。如果您的数据透视表将月份作为行标签，则会发生这种情况:

行上下文改变了 [CurrentDate] 的输出。不是我们想要的。所以你需要像这样覆盖过滤条件。

[CurrentDate] = CALCULATE(LASTDATE(TransactionTable[Posting Date])
                          ,ALL(TransactionTable)
                          )

然后你需要FILTER()

然后为了求和 [CurrentDay] 的金额，我们这样做:

[SumAmountCurrentDay] = CALCULATE([SumAmount]
                       ,FILTER(TransactionTable
                               ,TransactionTable[Posting Date]=[CurrentDay]
                               )
                        )

我们需要使用 FILTER() 因为它是一个比 CALCULATE 默认可以处理的更复杂的标准。 FILTER() 明确告诉 CALCULATE() 它需要过滤哪个表 - 虽然这对我们来说可能很明显，但对 PowerPivot 来说却不是。

这是您需要的其余公式，它们的复杂性各不相同，但主要是重复使用上面列出的函数，以及 ALL() 和 FILTER()。

[FirstDayOfCurrentMonth]

=CALCULATE(STARTOFMONTH(TransactionTable[Posting Date])
           ,ALL(TransactionTable)
           ,FILTER(TransactionTable
                   ,TransactionTable[Posting Date]=[CurrentDay]
                   )
           )

[SumAmountCurrentMonth]

=CALCULATE([SumAmount]
           ,DATESBETWEEN(DateTable[Date]
                         ,[FirstDayOfCurrentMonth]
                         ,[CurrentDay]
                         )
           )

[FirstDayOfPrevMonth]

=CALCULATE(STARTOFMONTH(TransactionTable[Posting Date])
           ,ALL(TransactionTable)
           ,FILTER(TransactionTable
                   ,TransactionTable[Posting Date]=
                         CALCULATE(dateadd(LASTDATE(TransactionTable[Posting Date])
                                           ,-1
                                           ,month
                                           )
                                   ,ALL(TransactionTable)
                                   )
                   )
            )

[LastDayOfPrevMonth]

=CALCULATE(ENDOFMONTH(TransactionTable[Posting Date])
          ,ALL(TransactionTable)
          ,FILTER(TransactionTable
                  ,TransactionTable[Posting Date]=
                        CALCULATE(dateadd(LASTDATE(TransactionTable[Posting Date])
                                          ,-1
                                          ,month
                                          )
                                   ,ALL(TransactionTable)
                                   )
                  )
            )

SumAmountPrevMonth

=CALCULATE([SumAmount]
           ,DATESBETWEEN(DateTable[Date]
                         ,[FirstDayOfPrevMonth]
                         ,[LastDayOfPrevMonth]
                          )
            )