cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Odilon Frequent Visitor
Frequent Visitor

Compare Actual and Forecast Weather Temperatures

I am looking for the optimal way to compare observed and forecast weather temperatures and visualize both on one chart.

My data is in one table as follows:

 

Station   Type        Day       Temperature

 

A            actual      6/4/18   70

A            actual      6/5/18   75

A            forecast   6/4/18   69

A            forecast   6/5/18   72

B            actual      6/4/18   70

B            actual      6/5/18   75

B            forecast   6/4/18   69

B            forecast   6/5/18   72

 

I want to configure a chart where I can pick a Station and display the actual and the forecast lines over the time period

 

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Compare Actual and Forecast Weather Temperatures

Hi @Odilon

Create calculated columns

DIFF =
DATEDIFF ( [Report_Day], [Horizon_Day ], DAY )
MATCH =
VAR COL1 =
    CALCULATE (
        MAX ( [Horizon_Day ] ),
        FILTER ( ALLEXCEPT ( Sheet1, Sheet1[Station] ), [DIFF] = 1 )
    )
RETURN
    IF ( [Horizon_Day ] = COL1 && [Report_Day] = COL110 )
FLAG =
IF ( [DIFF] = 2 || [MATCH] = 1, 1, 0 )

In Report view, add “FLAG” to visual level filters

2.png

3.png

 

 

Best Regards

Maggie

View solution in original post

9 REPLIES 9
BKirsch12 Member
Member

Re: Compare Actual and Forecast Weather Temperatures

You can create two measures to do this:

 

Replace TableName with your table name 🙂 

 

Forecast = CALCULATE(SUM(TableName[Temperature]),FILTER(TableName,FIND("forecast", TableName[Type],,0)))

 

Actual = CALCULATE(SUM(TableName[Temperature]),FILTER(TableName,FIND("actual", TableName[Type],,0)))

 

Extra Tip

 

You can then calculate how far off predictions are by using the measures:

 

Forecast missed by:

 

 = ABS(forecast - Actual)

 

Add the date and the measures to your chart, and you'll be set to go.

 

Let me know if you have any other questions.

 

 

Odilon Frequent Visitor
Frequent Visitor

Re: Compare Actual and Forecast Weather Temperatures

Thank you very much!

 

A complication in my data set is that there is an observed temperature for each date but that the forecast temperature is missing for some days.

 

I would therefore like to align dates for observed and forecast before plotting.

 

Thoughts?

Super User
Super User

Re: Compare Actual and Forecast Weather Temperatures

Hi,

 

I believe this is the visual you want.  I have written a simple measure

 

=Temp = SUM(Data[Temperature])

 

Hope this helps.

 

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Odilon Frequent Visitor
Frequent Visitor

Re: Compare Actual and Forecast Weather Temperatures

 

 

 

The next complication is that everyday forecasts are made for the following 3 days.

I would like to compare forecast and observed temperatures on a given day with a specific forecasting horizon.

E.g. how well did 2 days-ahead forecasts fit observed data.

 

I am currently tinking of doing this by creating a new column:

Lead_Forecast = Horizon_Day - Report_Day

 

My data set looks like:

 

Station   Type        Report_Day      Horizon_Day       Temperature

 

A            actual      6/4/18              6/4/18                    70

A            actual      6/5/18              6/5/18                    75

A            actual      6/6/18              6/6/18                    68

A            forecast   6/3/18              6/4/18                    69

A            forecast   6/3/18              6/5/18                    72

A            forecast   6/4/18              6/5/18                    67

B            actual      6/4/18              6/4/18                    71

B            actual      6/5/18              6/5/18                    72

B            actual      6/6/18              6/6/18                    66

B            forecast   6/3/18              6/4/18                    67

B            forecast   6/3/18              6/5/18                    71

B            forecast   6/4/18              6/5/18                    68

Super User
Super User

Re: Compare Actual and Forecast Weather Temperatures

Hi,

 

I do not understand your question.  I hope someone else helps you.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Odilon Frequent Visitor
Frequent Visitor

Re: Compare Actual and Forecast Weather Temperatures

To clarify:

 

I want to plot the line of the forecasts I made 2 days ahead vs. the line of my observed temperatures for each day

 

This means my forecasts line would be such that Horizon_Day = Report_Day + 2

 

 

So I would plot the Temperatures for rows (#) below:

 

 

#  Station   Type        Report_Day      Horizon_Day       Temperature

5   A            forecast   6/3/18              6/5/18                    72

compared to

2   A            actual      6/5/18              6/5/18                    75

 

or

 

#  Station   Type        Report_Day      Horizon_Day       Temperature

12 B            forecast   6/3/18              6/5/18                    71

compared to

9   B            actual      6/5/18              6/5/18                    72

 

 

 

#  Station   Type        Report_Day      Horizon_Day       Temperature

 

1   A            actual      6/4/18              6/4/18                    70

2   A            actual      6/5/18              6/5/18                    75

3   A            actual      6/6/18              6/6/18                    68

4   A            forecast   6/3/18              6/4/18                    69

5   A            forecast   6/3/18              6/5/18                    72

6   A            forecast   6/4/18              6/5/18                    67

7   B            actual      6/3/18              6/3/18                    62

8   B            actual      6/4/18              6/4/18                    71

9   B            actual      6/5/18              6/5/18                    72

10 B            actual      6/6/18              6/6/18                    66

11 B            forecast   6/3/18              6/4/18                    67

12 B            forecast   6/3/18              6/5/18                    71

13 B            forecast   6/4/18              6/5/18                    68

Community Support Team
Community Support Team

Re: Compare Actual and Forecast Weather Temperatures

Hi @Odilon

Create calculated columns

DIFF =
DATEDIFF ( [Report_Day], [Horizon_Day ], DAY )
MATCH =
VAR COL1 =
    CALCULATE (
        MAX ( [Horizon_Day ] ),
        FILTER ( ALLEXCEPT ( Sheet1, Sheet1[Station] ), [DIFF] = 1 )
    )
RETURN
    IF ( [Horizon_Day ] = COL1 && [Report_Day] = COL110 )
FLAG =
IF ( [DIFF] = 2 || [MATCH] = 1, 1, 0 )

In Report view, add “FLAG” to visual level filters

2.png

3.png

 

 

Best Regards

Maggie

View solution in original post

Odilon Frequent Visitor
Frequent Visitor

Re: Compare Actual and Forecast Weather Temperatures

Great! Thank you.

 

The solution I currently coded is similar but less advanced. I will now investigate how features you suggest like ALLEXCEPT etc. work.

 

My current code:

 

Lead Forecast = IF(('Temp Obs Fore Day'[Weather Date Long].[Date] - 'Temp Obs Fore Day'[Report Date Time].[Date])>0, DATEDIFF('Temp Obs Fore Day'[Report Date Time].[Date],'Temp Obs Fore Day'[Weather Date Long].[Date],DAY), -DATEDIFF('Temp Obs Fore Day'[Weather Date Long].[Date],'Temp Obs Fore Day'[Report Date Time].[Date],DAY))

 

Forecast = CALCULATE(average('Temp Obs Fore Day'[Temperature]),FILTER('Temp Obs Fore Day',FIND("Forecast", 'Temp Obs Fore Day'[WeatherTypeCd],,0)))

 

In Report view:

 

I filter on Lead Forecast

Odilon Frequent Visitor
Frequent Visitor

Re: Compare Actual and Forecast Weather Temperatures

How would I now calculate the difference between Actual and Forecast (2 days ahead) Weather so I can chart it?

 

I have (Lead Forecast below is equivalent to the DIFF variable in the previous post):

 

Forecast 2 Days = CALCULATE(AVERAGE('Temp Obs Fore Day'[Temperature]),FILTER('Temp Obs Fore Day',FIND("Forecast", 'Temp Obs Fore Day'[WeatherTypeCd],,0) && ('Temp Obs Fore Day'[Lead Forecast]=2)))

 

Observed Confirmed = CALCULATE(average('Temp Obs Fore Day'[Temperature]),FILTER('Temp Obs Fore Day',FIND("Actual", 'Temp Obs Fore Day'[WeatherTypeCd],,0) && ('Temp Obs Fore Day'[Lead Forecast]=0)))

 

Forecast Error = [Forecast 2 Days] - [Observed Confirmed]

 

But Forecast Error does not give me the expected result (it should be the difference) probably because I need to somehow "align" Forecast 2 Days and Observed Confirmed on Horizon_Day (note in my sample there may not be forecasts and/or actuals for all dates)

Helpful resources

Announcements
New Kudos Received Badges Coming

New Kudos Received Badges Coming

Kudos to you if you earned one of these! Check your inbox for a notification.

Microsoft Implementation for Communities Wins Award

Microsoft Implementation for Communities Wins Award

Learn about the award-winning innovation that was implemented across Microsoft’s Business Applications Communities.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Kudoed Authors (Last 30 Days)
Users online (1,245)