Description Forecasting is used to predict next points in time series data sets. The prediction in this example is based on trend and seasonality. Prerequisites (The sample .pbix files will not work without these prerequites completed) 1. Install R Engine Power BI Desktop does not include, deploy or install the R engine. To run R scripts in Power BI Desktop, you must separately installR on your local computer. You can download and install R for free from many locations, including the Revolution Open download page, and the CRAN Repository. 2. Install the required R packages. Download the R script attached to this message and run it to install all required packages on your local machine. Required R packages: forecast, graphics, scales, zoo Tested on: CRAN 3.3.1, MRO 3.3.0, powerbi.com Legal Disclaimers: Terms of Service and Third Party Programs.

To those who have issues with "Error in if (frequency > 1 && abs(frequency - round(frequency)) < ts.eps) frequency <- round(frequency) :" it turns out R uses your local language setting to determine a month name... If you add the following to you R-script it should force the code to use english setting:Sys.setlocale("LC_ALL","English")Make sure to add it the to top of the code Hope this helps you too :

To those who have issues with "Error in if (frequency > 1 && abs(frequency - round(frequency)) < ts.eps) frequency <- round(frequency) :" it turns out R uses your local language setting to determine a month name... If you add the following to you R-script it should force the code to use english setting:Sys.setlocale("LC_ALL","English")Make sure to add it the to top of the code Hope this helps you too : /Chris

You didn't call "pbiviz new" command, instead you are updating the existing one. So you'll need to do two things: 1) change "guid" field inside "pbiviz.json" to be different string 2) call "pbiviz package" command from command line ( inside the folder of visual) See more here https://github.com/Microsoft/PowerBI-visuals/tree/master/RVisualTutorial

Hi, I am fairly new to Power BI and don't know a lot about R scripting yet. I downloaded the file, but it gives me an error in the left visual: there is no package called 'scales'. I also downloaded the R script that I assume installs these packages. But how do I run the script to create them? I am using C:\Program Files\Microsoft\MRO-3.3.2 as my home directory and Visual Studio 2015 as my R IDE.

is it possible to plot one forecasted series and one actual values series on the same chart? It doesn't seem like Power BI's built-in visual supports this, but maybe an R-visual would.

Forecasting | Microsoft Fabric Community

30 Replies

hohlick
Continued Contributor
10 years ago
Do not sure why, but visual don't work:

Loading required package: scales
Loading required package: forecast
Loading required package: zoo
Attaching package: 'zoo'
The following objects are masked from 'package:base':
as.Date, as.Date.numeric
Loading required package: timeDate
Loading required package: methods
This is forecast 7.1
Error in if (frequency > 1 && abs(frequency - round(frequency)) < ts.eps) frequency <- round(frequency) :
- Anonymous
  Not applicable
  10 years ago
  To those who have issues with "Error in if (frequency > 1 && abs(frequency - round(frequency)) < ts.eps) frequency <- round(frequency) :" it turns out R uses your local language setting to determine a month name...
  If you add the following to you R-script it should force the code to use english setting:
  Sys.setlocale("LC_ALL","English")
  Make sure to add it the to top of the code
  
  Hope this helps you too :
  
  /Chris
HMonteiro
New Member
10 years ago
Hi, great piece of software,

All other models work, except this one, probably with the ts code part.

Posting the error message file here:

Error Message:
Erro de script do R.
Loading required package: scales
Loading required package: forecast
Loading required package: zoo
Attaching package: 'zoo'
The following objects are masked from 'package:base':
as.Date, as.Date.numeric
Loading required package: timeDate
Loading required package: methods
This is forecast 7.1
Error in if (frequency > 1 && abs(frequency - round(frequency)) < ts.eps) frequency <- round(frequency) :
missing value where TRUE/FALSE needed
Calls: ts
Execution halted

Stack Trace:

Invocation Stack Trace:

ID de atividade
e5ab37b4-ff68-4eeb-9ac7-d3cc346b8037
Hora
Fri Aug 19 2016 00:14:08 GMT+0100 (Hora de Verão de GMT)
Versão
2.37.4464.361 (PBIDesktop)
Client Error Code
ServiceErrorToClientError
Error Details
Erro de script do R.
Loading required package: scales
Loading required package: forecast
Loading required package: zoo
Attaching package: 'zoo'
The following objects are masked from 'package:base':
as.Date, as.Date.numeric
Loading required package: timeDate
Loading required package: methods
This is forecast 7.1
Error in if (frequency > 1 && abs(frequency - round(frequency)) < ts.eps) frequency <- round(frequency) :
missing value where TRUE/FALSE needed
Calls: ts
Execution halted

Stack Trace
Microsoft.PowerBI.ExploreServiceCommon.ScriptHandlerException: Erro de script do R.
Loading required package: scales
Loading required package: forecast
Loading required package: zoo
Attaching package: 'zoo'
The following objects are masked from 'package:base':
as.Date, as.Date.numeric
Loading required package: timeDate
Loading required package: methods
This is forecast 7.1
Error in if (frequency > 1 && abs(frequency - round(frequency)) < ts.eps) frequency <- round(frequency) :
missing value where TRUE/FALSE needed
Calls: ts
Execution halted
---> Microsoft.PowerBI.Radio.RScriptRuntimeException: Erro de script do R.
Loading required package: scales
Loading required package: forecast
Loading required package: zoo
Attaching package: 'zoo'
The following objects are masked from 'package:base':
as.Date, as.Date.numeric
Loading required package: timeDate
Loading required package: methods
This is forecast 7.1
Error in if (frequency > 1 && abs(frequency - round(frequency)) < ts.eps) frequency <- round(frequency) :
missing value where TRUE/FALSE needed
Calls: ts
Execution halted
em Microsoft.PowerBI.Radio.RScriptWrapper.RunScript(String originalScript, Int32 timeoutMs)
em Microsoft.PowerBI.Client.Windows.R.RScriptHandler.GenerateVisual(String script, String inputVariableName, IDataReader dataReader, Nullable`1 viewportWidthPx, Nullable`1 viewportHeightPx)
--- Fim do rastreio da pilha de excepção interna ---
em Microsoft.PowerBI.Client.Windows.R.RScriptHandler.GenerateVisual(String script, String inputVariableName, IDataReader dataReader, Nullable`1 viewportWidthPx, Nullable`1 viewportHeightPx)
em Microsoft.PowerBI.ExploreServiceCommon.ScriptVisualCommandFlow.RunInternal(Stream dataShapeResultStream, QueryBindingDescriptor& bindingDescriptor)
em Microsoft.PowerBI.ExploreServiceCommon.ScriptVisualCommandFlow.Run(Stream dataShapeResultStream, QueryBindingDescriptor& bindingDescriptor)
em Microsoft.PowerBI.ExploreHost.SemanticQuery.ExecuteSemanticQueryFlow.TransformDataShapeResult(QueryCommand transformCommand, String dataShapeId, SemanticQueryDataShapeCommand command, Stream dataShapeResultStream, QueryBindingDescriptor& bindingDescriptor)
em Microsoft.PowerBI.ExploreHost.SemanticQuery.ExecuteSemanticQueryFlow.ProcessAndWriteDataQuery(IQueryResultDataWriter queryResultDataWriter, DataShapeGenerationContext dsqGenContext, EntityDataModel model, DataQuery query, ServiceErrorStatusCode& serviceErrorStatusCode)
em Microsoft.PowerBI.ExploreHost.SemanticQuery.ExecuteSemanticQueryFlow.ProcessAndWriteSemanticQueryCommands(IQueryResultsWriter queryResultsWriter, ExecuteSemanticQueryRequest request, IConceptualSchema conceptualSchema, EntityDataModel model)

If only one time series selected, visual does appear, but prediction model transforms into an useless predictive model.

Keep it up with the good work.
- Anonymous
  Not applicable
  10 years ago
  To those who have issues with "Error in if (frequency > 1 && abs(frequency - round(frequency)) < ts.eps) frequency <- round(frequency) :" it turns out R uses your local language setting to determine a month name...
  If you add the following to you R-script it should force the code to use english setting:
  Sys.setlocale("LC_ALL","English")
  Make sure to add it the to top of the code
  
  Hope this helps you too :
  - hohlick
    Continued Contributor
    10 years ago
    Thank you Anonymous, it works now!
stlecho
New Member
10 years ago
When running the ScriptInstallPackagesForForecast.R script, I'm getting the following warnings:
1: package 'forecast' was built under R version 3.3.1
2: package 'zoo' was built under R version 3.3.1

How can this be solved please ?
stlecho
New Member
10 years ago
When running the ScriptInstallPackagesForForecast.R script, I'm getting the following warnings:
1: package 'forecast' was built under R version 3.3.1
2: package 'zoo' was built under R version 3.3.1

How can this be solved please ?
stlecho
New Member
10 years ago
When running the ScriptInstallPackagesForForecast.R script, I'm getting the following warnings:
-package ‘forecast’ was built under R version 3.3.1
-package ‘zoo’ was built under R version 3.3.1

What should I do to solve those warnings ?
- hohlick
  Continued Contributor
  10 years ago
  stlecho, nothing, it works with this warnings too
rohuyn
Frequent Visitor
9 years ago
This is great! Appreciate the time and effort it took to build and share this out!

I've been exploring the forecasting using the R integration and PowerBI, and was wondering if there was a way to explicitly show the predicted values and confidence intervals in a dynamic way. For example, in this situation, if I clicked on "Cuba" for this specific forecast example, how would I obtain the values shown in the R visualization on the Dashboard?

Thanks,
Ronnie
TravisKale
Helper I
9 years ago
Thanks for the great visual.

I am getting weird x Axis (date) labels. 2016.2, 2016.4 and they don't seem to line up with February 2016 and April 2016. Attached is a png image of what i am seeing. I also included a standard pbi visual to show the correct dates.

Am I missing a formatting option?
- mjc
  Frequent Visitor
  9 years ago
  Attached is a copy of the actual data used in the original births.csv file which I obtained from the pbix.
  
  I got this by simply creating a new table on the report page, added all columns with no summarisation then used the export data option from that table.
  As dates are exported from Power BI using a date-time format I used excel to reformat the Calendar Month column as just a date. I deliberately used a US format (mm//dd/yyyy) which might be more widely usable though this should automatically be recognised by Power BI anyway. If it is not and you use something different then just format in excel to suit your needs.
  
  Obvioulsy this doesn't really gain you anything since the information was all already embedded in the pbix but it does allow you to fix the errors seen when looking at queries.
  
  Of course, the technique is something that might be useful in other situations where you are missing a source file and want to recreate it so hopefully this helps someone.
  
  (attached as a zip file since csv attachments are not supported on the forum)
  births.from.pbix.csv.zip39 KB
Anonymous
Not applicable
9 years ago
Hi there!
First of all, awesome visual! It took me a week to script the ets forecasting in R on my data, and I've been able to get what I want, but then the problem is that it generates a completely new set of data, unrealted to the existing model in Power BI. Your visual solves this problem by integrating the R script (very similar to what I have) into Power BI, so now I can use measures. Great work.

Afew things missing here: the Y-axis is poorly marked, if we're talking about millions of dollars on the Y axis. Also, no labels. So, I decided to clone this visual and create my small variation. Standing on the shoulders of giants, as they say

All I've done so far is adjusted the R script to also populate a table of values along with the graph. I left the rest untouched.
I got to the point that suits me for now (I hope to keep improving) and I can see needed results on the web when I run pbiviz start command.
But when I try to package the visual, the packaged custom visual for PBI Desktop doesn't have any of those changes... What am I doing wrong?
- boefraty
  Microsoft Employee
  9 years ago
  You didn't call "pbiviz new" command, instead you are updating the existing one. So you'll need to do two things:
  
  1) change "guid" field inside "pbiviz.json" to be different string
  
  2) call "pbiviz package" command from command line ( inside the folder of visual)
  
  See more here
  
  https://github.com/Microsoft/PowerBI-visuals/tree/master/RVisualTutorial
  - thefal
    Frequent Visitor
    9 years ago
    Hi,
    
    I am fairly new to Power BI and don't know a lot about R scripting yet. I downloaded the file, but it gives me an error in the left visual: there is no package called 'scales'. I also downloaded the R script that I assume installs these packages. But how do I run the script to create them?
    
    I am using C:\Program Files\Microsoft\MRO-3.3.2 as my home directory and Visual Studio 2015 as my R IDE.
ScottFerguson
Helper II
9 years ago
I copied the Power BI page, and replaced the R visual with a trend line using the forecasting functionality in the Analytics (second image). After removing the date hierarchy setting from the x-axis - the forecasts look similar.

Filtered for U.S.
- ironryan77
  Kudo Commander
  9 years ago
  is it possible to plot one forecasted series and one actual values series on the same chart? It doesn't seem like Power BI's built-in visual supports this, but maybe an R-visual would.
  - ScottFerguson
    Helper II
    9 years ago
    The built-in forecast feature does include the option to include actual side-by-side with projections.
    Does anyone know if the engince that drives analytics is any different from R? The results look the same, so it would appear not.
- boefraty
  Microsoft Employee
  9 years ago
  See also:
  https://community.powerbi.com/t5/R-Script-Showcase/Forecasting-in-R-Visual-R-Custom-Visual-Power-Query-Line-Chart/m-p/104473