how to calculate the price from extract day minus 3 day

[Anonymous]

How to write DAX formula to calculate the price from extract day - 3 days

I use the below formula and not getting the correct answer

3dayPrice = CALCULATE(AVERAGE(PowerBI_HistoricalMarketPricing[DailyRate]),FILTER(PowerBI_HistoricalMarketPricing,DATEADD(PowerBI_HistoricalMarketPricing[Extract Date].[Date],-3,DAY)))

[Anonymous]

Hi @amitchandak ,
This formula will give me only for a max date - 3 days But I need the values for every extract date and it's 3day back price in the table

Car	Extractdate	Price	3day back price
Mid size	22/06/2021	133.23	94.1
Mid size	21/06/2021	124.47	144.23
Mid size	20/06/2021	112.18	118.65
Mid size	19/06/2021	94.1	98.35
Mid size	18/06/2021	144.23	115.42
Mid size	17/06/2021	118.65
Mid size	16/06/2021	98.35
Mid size	15/06/2021	115.42

[v-yiruan-msft · Accepted solution]

Hi @Anonymous ,

You can create a measure or calculated column as below to get it:

Measure:

3day back price = 
VAR _curdate =
    SELECTEDVALUE ( 'Table'[Extractdate] )
RETURN
    CALCULATE (
        MAX ( 'Table'[Price] ),
        FILTER (
            ALLSELECTED ( 'Table' ),
            'Table'[Car] = SELECTEDVALUE ( 'Table'[Car] )
                && DATEDIFF ( 'Table'[Extractdate], _curdate, DAY ) = 3
        )
    )

Calculated column:

Column = 
    CALCULATE (
        MAX ( 'Table'[Price] ),
        FILTER (
            ALLSELECTED ( 'Table' ),
            'Table'[Car] = EARLIER( 'Table'[Car] )
                && DATEDIFF ( 'Table'[Extractdate], EARLIER('Table'[Extractdate]), DAY ) = 3
        )
    )

Best Regards

[amitchandak]

@Anonymous , Try with a date table

CALCULATE(AVERAGE(PowerBI_HistoricalMarketPricing[DailyRate]),DATEADD(Date[Date],-3,DAY))

or

CALCULATE(AVERAGE(PowerBI_HistoricalMarketPricing[DailyRate]),FILTER(all(PowerBI_HistoricalMarketPricing),PowerBI_HistoricalMarketPricing[Extract Date] =max(PowerBI_HistoricalMarketPricing[Extract Date]) -3))