Average price per product and city for last n days

[Divous]

Hi community,

I have table:

and need to add column with output:

AveragePrice = last_7_days / City / Product

I think I am doing right average per product:

Average_price_last_7_days = 
VAR first_Date =
    MAX ('Sales table'[Date of sale])
    RETURN 
        CALCULATE(
            AVERAGE('Sales table'[Price]),
            ALLEXCEPT('Sales table','Sales table'[Product]),
            'Sales table'[Date of sale] <= first_Date
                && 'Sales table'[Date of sale] >= first_Date - 7)

but how to put in one formula with city?

Thanks in advance

Divous

[v-cazheng-msft]

Hi, @Divous 

Is your problem solved? If pranit828's post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Best Regards,

Caiyun Zheng

[Anonymous · Accepted solution]

HI @Divous 

try

Average_price_last_7_days = 
VAR first_Date =
    MAX ('Sales table'[Date of sale])
    RETURN 
        CALCULATE(
            AVERAGE('Sales table'[Price]),
            ALLEXCEPT('Sales table','Sales table'[Product],'Sales table'[City]),
            'Sales table'[Date of sale] <= first_Date
                && 'Sales table'[Date of sale] >= first_Date - 7)