Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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
Hi,
I believe this is the visual you want. I have written a simple measure
=Temp = SUM(Data[Temperature])
Hope this helps.
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
Hi,
I do not understand your question. I hope someone else helps you.
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
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
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)
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
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.
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?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
106 | |
97 | |
80 | |
67 | |
63 |
User | Count |
---|---|
147 | |
110 | |
108 | |
85 | |
64 |