Forecasting using average revenue based on product type, location, and weekday

Hello,

I am trying to caluclate an average for all future dates based off of past dates this month, with respect to three filters: product type, location name, and weekday. 

Query1:

 

 

For *one specific product type*, filtering for only the product_type "on demand", here is my layout so far.

You can see the predictions are working correctly, except that they are not taking into account the weekday! I think this is simply because it is not part of the matrix? I have tried everything I can think of, and this is the measure I am using right now:

Forecast =

VAR AvgRev = CALCULATE( AVERAGE(Query1[revenue]), ALLEXCEPT(Query1, Query1[location_name], Query1[product_type], Query1[weekday]))

RETURN

    IF(

        MAX('Calendar'[Date]) < TODAY() ,

        --- THEN ---

        MAX('Query1'[revenue]) ,

        --- ELSE ---

        AvgRev

)

 

I have a calendar table seen earlier which is related on date (many) to Date (one):

 

The forecast matrix right now uses Date from the calendar table, but is being told to filter with weekday from "Query1", although because of the relationship I would think that shouldn't be an issue. 

 

I would appreciate any help! At the end of this, I will have to get total revenue sums for each location on each product_type. I am not sure if that changes the approach I should use. 

 

Thank you!