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

[nhmpp]

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. 

Here is a sample of what each table looks like:

Calendar:

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!

[v-xiaosun-msft · Accepted solution]

Hi @nhmpp ,

According to your description, here is my suggestion.

Please establish a relationship between two tables with "weekday".

Then it will work.

Best Regards,
Community Support Team _ xiaosun

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.