Avg Temp for specifc dates

[ebrownretail]

Hi!

I am trying to pull the average temperature for a specific date/city so we can see how far above or below tomorrows forecast is. Any ideas? i tried the below, but it did not work. 

Avg Temp =

CALCULATE(AVERAGEX('weather history', 'weather history'[temp]), FILTER(DATESINPERIOD(Dates[Date], SELECTEDVALUE('weather history'[datetime]) , -5, YEAR), 'weather history'[datetime]))

[Anonymous]

Hi @ebrownretail , thanks for the reply from lszymk97.

Please try the following measure:

Avg Temp =
CALCULATE(
   AVERAGEX(
       'weather history',
       'weather history'[temp]
   ),
   FILTER(
       'weather history',
       'weather history'[datetime] >= DATEADD(SELECTEDVALUE('weather history'[datetime]), -5, YEAR) &&
       'weather history'[datetime] <= SELECTEDVALUE('weather history'[datetime])
   )
)

Best regards,

Joyce

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

[lszymk97]

Hello,
Can you try to use below? Your formula references a 'Dates' table in the DATESINPERIOD function, but then tries to filter the 'weather history' table. 

Avg Temp = CALCULATE( AVERAGE('weather history'[temp]), FILTER( ALL('weather history'), AND( 'weather history'[city] = SELECTEDVALUE('weather history'[city]), 'weather history'[datetime] <= SELECTEDVALUE('weather history'[datetime]), 'weather history'[datetime] > DATEADD(SELECTEDVALUE('weather history'[datetime]), -5, YEAR) ) ) )

[ebrownretail]

i got this error for this