Power BI DAX 计算所有筛选选项的上周销售额 [英] Power BI DAX Calculating Last week Sales for All the Filter Options

问题描述

我有以下数据结构

Date           | Category      | Sales Amount 
----------------------------------------------------
01-Sep-2016    | Food          | 100
02-Sep-2016    | Food          | 120
03-Sep-2016    | Food          | 130
01-Sep-2016    | Electricity   | 180
02-Sep-2016    | Electricity   | 60
01-Sep-2016    | Perfumes      | 80
02-Sep-2016    | Perfumes      | 40

我想计算每个类别的两周销售额，将来我可能还会添加另一个列，例如区域".我使用了以下公式，如果我只选择日期但如果我选择类别则不起作用.

I want to calculate the Two Week Sales for Each Category, I might add another column like Territory as well in the future. I used following Formula which worked fine if I only select Date but Does Not Work if I select Category.

SalesTwoWeeksAgo =
CALCULATE (
    SUM ( 'Table'[SalesAmount] ),
    FILTER (
        ALL ( 'Table' ),
        COUNTROWS (
            FILTER (
                'Table',
                EARLIER ( 'Table'[Date] ) = DATEADD ( 'Table'[Date], -14, DAY )
            )
        )
    )
)

以上公式由 alejandro zuleta 提供，链接为

The Above Formula was contributed by alejandro zuleta and link is

Power BI 获得 2 周后同日值

推荐答案

如果我理解您的问题，问题是您有一个 Category 列，因此您需要在两周内重新获得销售额在表达式中计算的当前类别值中的时间.您只需在 FILTER 函数中添加一个附加条件，以将当前 Category 和当前 Date 减去 14 天，然后它将返回SUM 函数的相关 Sales Amount 值.

If I understand your question, the problem is that you have a Category column so you need to get the sales two weeks back in the time in the current category value evaluated in the expression. You just have to add an additional condition in the FILTER function to take the current Category and the current Date substracting 14 days, then it will return the related Sales Amount values to the SUM function.

SalesTwoWeeksAgo =
CALCULATE (
    SUM ( 'Table'[Sales Amount] ),
    FILTER (
        ALL ( 'Table' ),
        COUNTROWS (
            FILTER (
                'Table',
                EARLIER ( 'Table'[Date] ) = DATEADD ( 'Table'[Date], -14, DAY )
                    && 'Table'[Category] = EARLIER ( 'Table'[Category] )
            )
        )
    )
)

此外，如果您将 Territory 列添加到表中，您可能需要在每个 Date、 的两周前获取 Sales AmountCategory 和 Territory 所以你只需要在 FILTER 函数中添加第三个条件.

Also if you add Territory column to your table you may need to get the Sales Amount two weeks before per Date, Category and Territory so you just need to add a third conditional in the FILTER function.

SalesTwoWeeksAgo =
CALCULATE (
    SUM ( 'Table'[Sales Amount] ),
    FILTER (
        ALL ( 'Table' ),
        COUNTROWS (
            FILTER (
                'Table',
                EARLIER ( 'Table'[Date] ) = DATEADD ( 'Table'[Date], -14, DAY )
                    && 'Table'[Category] = EARLIER ( 'Table'[Category] )
                    && 'Table'[Territory] = EARLIER ( 'Table'[Territory] )
            )
        )
    )
)

此处提供的解决方案尚未经过测试，但希望它是您所需要的.

The solution provided here is not tested yet but hopefully it is what you need.

如果这有帮助，请告诉我.

Let me know if this helps.

这篇关于Power BI DAX 计算所有筛选选项的上周销售额的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！