Power BI DAX Calculating Last week Sales for All the Filter Options

Question

I have following Data Structure

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 )
            )
        )
    )
)

The Above Formula was contributed by alejandro zuleta and link is

Power BI getting 2 week back same day value

You understood correctly in your response. Thanks it worked like a charm — Janjua

alejandro zuleta alejandro zuleta · Accepted Answer · 2016-10-19T03:01:22

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] )
            )
        )
    )
)

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 Calculating Last week Sales for All the Filter Options

1 Answers