Forum Discussion

ebrownretail's avatar
ebrownretail
Icon for Resolver I rankResolver I
2 years ago

Avg Temp for specifc dates

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]))
Need Help

3 Replies

  • lszymk97's avatar
    lszymk97
    Frequent Visitor
    2 years ago

    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) ) ) )

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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.