Referring to another Table by calculating average

[furg442]

Hi community,

I have the following case:

Sales

Date	Sales	New Column (average Temperature)
2024-01	500
2024-02	300
2024-03	400

Weather_data:

Date	temperature
2024-01	3
2024-01	5
2024-01	2
2024-02	3
2024-02	11
2024-03	2
2024-03	3

The tables are connected (Column: date)

I want to create a new calculated Columnin table Sales, which calculates the average temperature each month. In this case, table Weather_data has multiple rows for each month and table Sales only has 1 row each month.

Example for first row (2024-01) in "Sales": 3+5+2 => average =>3.33

Example for second row (2024-02) in Sales: 3+11 => average => 7

Example for third row (2024-03) in Sales: 2+3 => average => 2.5

Expected result for the new column in table "Sales": 

Date	Sales	New Column (average Temperature)
2024-01	500	3.33
2024-02	300	7
2024-03	400	2.5

What would be the DAX function for the new calculated column?

Thank you!

Best

[Corey_M · Accepted solution]

Based on your sample data it would be this 

Avg Temp = 
CALCULATE(
AVERAGE(Weather_data[temperature]),
FILTER(
    Weather_data,
    Weather_data[Date] = Sales[Date]
))

however this was based on the date column you provided, if you are using an actual date instead of the year-month text in Weather_data I would highly recommend adding a calendar table.