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 和当前的日期减去14天，然后会将相关的销售金额值返回到 SUM 功能。

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 列到表中，您可能需要在每个日期，<$ c前两周获取销售金额 $ c>类别和领土，因此您只需要在 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屋！