skip to main content
Power BI
    • What is Power BI
    • Why Power BI
    • Customer stories
    • Data visuals
    • Security
    • Power BI Desktop
    • Power BI Pro
    • Power BI Premium
    • Power BI Mobile
    • Power BI Embedded
    • Power BI Report Server
  • Pricing
    • Azure + Power BI
    • Office 365 + Power BI
      • Energy
      • Healthcare
      • Manufacturing
      • Media
      • Retail
    • For analysts
    • For IT
      • Overview
      • Embedded analytics
      • Power BI visuals
      • Automation
      • Documentation
      • Community
    • Overview
    • Find consulting services
    • Partner showcase
    • Find a partner
    • Become a partner
    • Instructor-led training
    • Getting started
      • Overview
      • Online workshops
      • Self-guided learning
      • Webinars
      • Documentation
      • Roadmap
      • Overview
      • Issues
      • Give feedback
    • Blog
    • Business intelligence topics
    • Overview
    • Forums
    • Galleries
    • Submit ideas
    • Events
    • User groups
    • Community blog
    • Register
    • ·
    • Sign in
    • ·
    • Help
    Go To
    • Galleries
    • Community Connections & How-To Videos
    • COVID-19 Data Stories Gallery
    • Themes Gallery
    • Data Stories Gallery
    • R Script Showcase
    • Webinars and Video Gallery
    • Quick Measures Gallery
    • 2021 MSBizAppsSummit Gallery
    • 2020 MSBizAppsSummit Gallery
    • 2019 MSBizAppsSummit Gallery
    cancel
    Turn on suggestions
    Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
    Showing results for 
    Search instead for 
    Did you mean: 
    • Microsoft Power BI Community
    • Galleries
    • R Script Showcase
    • Forecasting

    Forecasting

    08-14-2016 13:16 PM - last edited 11-16-2016 01:27 AM

    Microsoft Sharon
    Microsoft
    87110 Views
    LinkedIn LinkedIn Facebook Facebook Twitter Twitter
    Sharon
    Microsoft Sharon
    Microsoft
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Email to a Friend
    • Report Inappropriate Content

    Forecasting

    ‎08-14-2016 01:16 PM

    report_forecastin.JPGDescription

    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.

     

    Preview file
    7 KB
    Forecasting Showcase.pbix
    ScriptInstallPackagesWithWorkaroundsForecast.R
    Labels:
    • Labels:
    • Advanced Analytics Visualizations
    Message 1 of 31
    87,110 Views
    15
    Reply
    • All forum topics
    • Previous Topic
    • Next Topic
    ScottFerguson
    ScottFerguson Helper II
    Helper II
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Email to a Friend
    • Report Inappropriate Content

    ‎03-14-2017 04:03 PM

    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.

    Forecasting comparison capture.JPG

     

     

    Message 25 of 31
    32,657 Views
    0
    Reply
    ironryan77
    ironryan77 Advocate II
    Advocate II
    In response to ScottFerguson
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Email to a Friend
    • Report Inappropriate Content

    ‎05-12-2017 01:59 PM

    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.

    Message 27 of 31
    32,540 Views
    0
    Reply
    ScottFerguson
    ScottFerguson Helper II
    Helper II
    In response to ironryan77
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Email to a Friend
    • Report Inappropriate Content

    ‎05-12-2017 02:35 PM

    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.

    Forecast Showcase Image - 12 months actual.JPGForecast Showcase Image - 12 months actual_2.JPG

    Message 28 of 31
    32,534 Views
    1
    Reply
    ironryan77
    ironryan77 Advocate II
    Advocate II
    In response to ScottFerguson
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Email to a Friend
    • Report Inappropriate Content

    ‎05-15-2017 06:18 AM

    No, this is what I'm saying.  For the built-in visual, the forecast option is only present when one series is plotted.

    forecast option missing.jpgforecast option present.jpg

    Message 29 of 31
    32,524 Views
    0
    Reply
    ScottFerguson
    ScottFerguson Helper II
    Helper II
    In response to ironryan77
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Email to a Friend
    • Report Inappropriate Content

    ‎05-15-2017 06:59 AM

    A work around is to overlap two individual charts - below you will see forecast for Sales and Product Cost from Adventure works. However, the legend is lost as each chart only has one variable.

    A trade-off with R charts I have noticed is that slicers do not apply to the chart. Has that changed?

    Forecast Showcase Image - Dual Forecasts.jpg

    Message 30 of 31
    32,523 Views
    1
    Reply
    ironryan77
    ironryan77 Advocate II
    Advocate II
    In response to ScottFerguson
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Email to a Friend
    • Report Inappropriate Content

    ‎05-15-2017 07:32 AM

    I did try this approach of using overlapping charts, but that doesn't work either.  What I want to do is to plot one forecast series and one actual series on the same (or overlapping) chart.  But the problem is that after I add a second series to the same line chart, the forecasting disappears.  In my chart, I'm trying to plot the actuals from January 2016 to December 2016; the other series from January 2017 to December 2017.  So the x-axis should be the same for both series, and this is why I need one actual and one forecast series.  Make sense?

     

    Another problem with overlapping is that tooltips will not work on the chart in back. 

    Yes, slicers do definitely apply to R visuals if that is what you're asking.  Not sure though if it's possible to plot one actual and one forecast series on the same chart.

    Message 31 of 31
    31,446 Views
    0
    Reply
    boefraty
    Microsoft boefraty
    Microsoft
    In response to ScottFerguson
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Email to a Friend
    • Report Inappropriate Content

    ‎04-23-2017 12:37 AM

    See also: 
    https://community.powerbi.com/t5/R-Script-Showcase/Forecasting-in-R-Visual-R-Custom-Visual-Power-Que...

     

    Message 26 of 31
    32,598 Views
    0
    Reply
    Anonymous
    Not applicable
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Email to a Friend
    • Report Inappropriate Content

    ‎12-23-2016 07:53 AM

    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.

    Capture1.PNG

    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?

    Capture2.PNG

    Message 20 of 31
    32,785 Views
    0
    Reply
    boefraty
    Microsoft boefraty
    Microsoft
    In response to Anonymous
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Email to a Friend
    • Report Inappropriate Content

    ‎12-25-2016 11:32 AM

    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

     

     

    Message 21 of 31
    32,789 Views
    1
    Reply
    thefal
    thefal
    Frequent Visitor
    In response to boefraty
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Email to a Friend
    • Report Inappropriate Content

    ‎02-26-2017 09:47 PM

    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.

    Message 22 of 31
    32,702 Views
    1
    Reply
    ironryan77
    ironryan77 Advocate II
    Advocate II
    In response to thefal
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Email to a Friend
    • Report Inappropriate Content

    ‎03-13-2017 01:37 PM

    I ran into this same problem initially.  What you have to do to fix:

    Update R for Windows from the R Script command line:

    if(!require(installr)) {
    install.packages("installr"); require(installr))

    It will tell you you're using 3.3.2, but the latest version is 3.3.3.  To update, run this command:

     

    updateR()

    Then open MRO 3.3.3 and load the scales package:

     

    This should install the zoo and forecast package for you, but if it doesn't manually install these packages from the screenshot above.

    Message 24 of 31
    32,670 Views
    0
    Reply
    boefraty
    Microsoft boefraty
    Microsoft
    In response to thefal
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Email to a Friend
    • Report Inappropriate Content

    ‎02-28-2017 02:09 AM

    I sent a reply to private mail. Hopefully it helps...  

     

    In general to install missing package you need to type install.packages("scales")   from any R command line (R studio or VS IDE). Alernatively, you run the script.

    See

    https://powerbi.microsoft.com/en-us/documentation/powerbi-desktop-r-powered-custom-visuals/

     

    Scroll down till you see:

    "Install R packages manually, using any R console"

     

    Message 23 of 31
    32,693 Views
    0
    Reply
    TravisKale
    TravisKale Helper I
    Helper I
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Email to a Friend
    • Report Inappropriate Content

    ‎11-10-2016 01:42 PM

    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?

     

     

     

    Preview file
    28 KB
    Message 18 of 31
    32,917 Views
    0
    Reply
    mjc
    mjc
    Frequent Visitor
    In response to TravisKale
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Email to a Friend
    • Report Inappropriate Content

    ‎11-16-2016 03:05 AM

    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.zip
    Message 19 of 31
    32,876 Views
    0
    Reply
    rohuyn
    rohuyn
    Frequent Visitor
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Email to a Friend
    • Report Inappropriate Content

    ‎10-10-2016 12:06 PM

    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

    Message 17 of 31
    33,001 Views
    0
    Reply
    stlecho
    stlecho
    New Member
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Email to a Friend
    • Report Inappropriate Content

    ‎08-19-2016 02:53 AM

    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 ?

    Message 15 of 31
    85,217 Views
    0
    Reply
    hohlick
    hohlick
    MVP
    In response to stlecho
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Email to a Friend
    • Report Inappropriate Content

    ‎08-19-2016 04:44 AM

    @stlecho, nothing, it works with this warnings too

    Maxim Zelensky
    excel-inside.pro
    Message 16 of 31
    85,182 Views
    0
    Reply
    stlecho
    stlecho
    New Member
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Email to a Friend
    • Report Inappropriate Content

    ‎08-19-2016 02:51 AM

    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 ?

    Message 14 of 31
    85,215 Views
    0
    Reply
    stlecho
    stlecho
    New Member
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Email to a Friend
    • Report Inappropriate Content

    ‎08-19-2016 02:48 AM

    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 ?

    Message 13 of 31
    85,214 Views
    0
    Reply
    HMonteiro
    HMonteiro
    New Member
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Email to a Friend
    • Report Inappropriate Content

    ‎08-18-2016 04:29 PM

    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.

    Message 4 of 31
    85,396 Views
    0
    Reply

    Power Platform

    • Overview
    • Power BI
    • Power Apps
    • Power Automate
    • Power Virtual Agents

    • Sign in
    • Sign up

    Browse

    • Solutions
    • Partners
    • Consulting Services

    Downloads

    • Power BI Desktop
    • Power BI Mobile
    • Power BI Report Server
    • See all downloads

    Learn

    • Guided learning
    • Documentation
    • Support
    • Community
    • Give feedback
    • Webinars
    • Developers
    • Blog
    • Newsletter

    © 2022 Microsoft

    Follow Power BI

    • Privacy & cookies
    • Manage cookies
    • Terms of use
    • Trademarks