问题

I'm using Power Query to pull some billing information from a Sharepoint Folder. The fisrt image shows this table.

我正在使用Power Query从Sharepoint文件夹中提取一些计费信息。第一张图片显示了这个表格。

I then want to summarise this with a Pivot Table based on the billing period.

然后，我想根据计费周期使用数据透视表对其进行汇总。

The second image shows my pivot table. I'm trying to create a measure 'Previous Month' that will give me the Total Charges for the previous month for each row - however I'm struggling. Please note - sometimes the different companies will be billed on different date periods - I still wish this to be based on the month the Bill Period End Date falls in.

第二张图片显示了我的数据透视表。我试图创建一个名为“Previous Month”的度量，该度量将为每一行提供上个月的总费用 - 但我遇到了困难。请注意 - 有时不同的公司将在不同的日期周期内收费 - 但我仍希望基于账单周期结束日期所在的月份来计算。

This is the measure that I've created, but as you can see it only provides the 'Apr' value in the Grand Total.

这是我创建的度量，但正如你所看到的，它只在总计中提供了“Apr”值。

=CALCULATE( SUM(Billing[Total Charges excl. Vat]), FILTER(Billing, Billing[Billing Period End Date (Month Index)] = MAX(Billing[Billing Period End Date (Month Index)])-1) )

我创建的度量如上所示，但如您所见，它只在总计中提供了“Apr”值。

=CALCULATE(
SUM(Billing[Total Charges excl. Vat]), 
FILTER(Billing,
Billing[Billing Period End Date (Month Index)] = MAX(Billing[Billing Period End Date (Month Index)])-1)
)

答案1

得分: 1

这是您要翻译的内容：

=VAR p = CALCULATE(MAX(Billing[Billing Period End Date (Month Index)])-1)
VAR s = CALCULATE( SUM(Billing[Total Charges Excl VAT]),FILTER(ALL(Billing),Billing[Billing Period End Date (Month Index)] = p))
RETURN
IF(HASONEVALUE(Billing[Billing Period End Date (Month Index)]), s)

=VAR p = CALCULATE(MAX(Billing[Billing Period End Date (Month Index)])-1)
VAR s = CALCULATE( SUM(Billing[Total Charges Excl VAT]),FILTER(ALL(Billing),Billing[Billing Period End Date (Month Index)] = p))
RETURN
IF(HASONEVALUE(Billing[Billing Period End Date (Month Index)]), s)