Compare Actual and Forecast Weather Temperatures

[Odilon]

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

[Ashish_Mathur]

Hi,

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

=Temp = SUM(Data[Temperature])

Hope this helps.

[Odilon]

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

[Ashish_Mathur]

Hi,

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

[Odilon]

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

[v-juanli-msft · Accepted solution]

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] = COL1, 1, 0 )

FLAG =
IF ( [DIFF] = 2 || [MATCH] = 1, 1, 0 )

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

Best Regards

Maggie

[Odilon]

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)

[Odilon]

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

[BKirsch12]

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]

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?