Power BI getting 2 week back same day value

Question

I need to create a measure in Power BI which will always provide the value of sales amount 2 weeks back for the same weekday.

e.g Today = Tuesday 27-Sep-2016 so I need value for Tuesday 13-Sep-2016

The Table is simple with following structure

 Date       |  SalesAmount
-----------------------------
01-Sep-2016 | 500
02-Sep-2016 | 450
03-Sep-2016 | 650

The answer I posted should work, however calculations that involve analysis through time are better defined using date/calendar tables in order to take advantage of DAX Time Intelligence functions. — alejandro zuleta
Your solution seems to work, I am verifying it and will update — Janjua

alejandro zuleta alejandro zuleta · Accepted Answer · 2016-09-27T16:45:43

You can use DATEADD() function to compute the 14 days before each date. Create a measure using the below code.

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

While the previous DAX expression works, I'd create a calculated column which is better than the measure approach regarding to performance.

Let me know if this helps.

Power BI getting 2 week back same day value

1 Answers