Power BI Forecasting using R in Microsoft D365 Business Central

We are going to keep going with Power BI, and right now I’ going to get into something bit heavy, I hope its ok with everyone. It’s just so much fun. I must do it.

I’m going to start playing around with R. R is a Mathematical library that you can use in Power BI and it can do anything you want mathematically with the data and presented with a nice chart. I’m going to show you how we can do Forecasting in Power BI using R.

We are still working on cash flows, so I wanted to cash flow forecast it. This, of course, it is putting in all the AR AP liquid funds etc., however, what I want to do is see if I can extend this chart to predict how the cash will go forward, and statistically tell me where it would be. What I'm looking for is a forecasting algorithm that would give me like a line with a sort of deviation, so I could see where the system thinks it going to and what the likelihoods are that it's going to stay in a certain parameter.

If you guys have looked at hurricane's charts, the path that the hurricane goes has the same type of prediction models, so it's like that that I want to get going.

R, we have R in here. I’m going to create a new page, throw in one of the tables, put in my cashflow date, renamed it CF date, I want to move that to that, and I have a running total right here. I have these two fields, the cash flow date and the Running total like we’ve been working on before. I want to graph that like I have on the other side, like here, this line running total all the way up, but I want to include a forecast and I want to do it using R. I cannot do that with any of the visuals here, they don’t allow me to do any forecasting or any type of math to that level, you could use, docs with a table or something else and I think it would get messy.

Visual forecast in R
Let's use R, in order to use it, I just click R here, and I get a visual. But what R comes with is this pane here, where I can drop in values, so I just check them off, I want the cash flow Date and the running total. So, you can see its scripting something. There is a little bit of programming here, not much a little bit. I’m going to use an R script, I’m going g to type it up for you, I’m going to explain a little bit how it works, and you can use what I used but anyways. To get this forecasting going I must include a library.

So, I type it and it’s called forecast. now it's in R, now if you just installed power BI with just a default R setting etc., if you try to compile this or get this to go, by clicking here, this arrow to run the script, nothing really happens. Here it gives me an error saying item not plotting anything but complains that it doesn’t find the forecasting module. So, to get that module, I recommend installing R studio. This thing. Ok.

If you want to go ahead and start working with this, that's fine, it's complicated. But with R studio you get this, it's free. You can just google it. And

you can go ahead and say install packages and then say forecast.
> install.packages(“forecast”) so if look here, I have my forecast packages. Now Power BI might complain a little bit more than missing all the packages, you are going to see it in the error message, whenever you try to run the script here, and you just add that package with the same install method that I just did. Until you got all the packages. That’s the annoying part that you must get through, but anyway, do it, once and you got it. and for a fantastic forecast what don’t we do?

Create a data set
What I’m going to do now, I’m going to create a data set. Which is going to aggregate the running total amount over cash flow date. That’s just punching it up per date. And then I see the date is equal to the data sub and the function is the sum. Them I’m going to create time series, so if you anything about forecasting, you need time series and its pretty easy, I’m just using the running total, so the function is, time series, data set, running total. Finally, I forecast. So, I do here, Forecast is equal to; These arrows just mean, equal to or puts the result into the variable; Forecast into Yes which is the final one. And then we just end up by applying it.

If I plot it now, here's an error. See if date not found, what am I doing wrong here? oversee of date. I included CF date here. The same problem, as usual, CF date I have no to have date hierarchy. Now I get it. I’m sure we all do this mistake a lot.

Forecast Graph
Here we have a graph, if I look at page 1, for example, is the same graph as this one, the top one. except what happens is that it continues, I think it counting the days and I might go into how we can fix the axes on R in the next video and make these dollars. But it continues and says, well the trend is that is going to go upwards, and the likelihood is that stays in this dark blue area and then it's unlikely that it goes into the grey and then it's very unlikely that it goes out of that. This is According to the data we have in here. So, without really knowing anything about the data, like where it came from, etc., etc. The system is predicting how it will go forward, based on here. So of course, the smaller the cone here, the more accurate the data is.

This is really cool you can use this for cash flow of course but why not use it for items? The actual sales data? or sales mount?. There are many different options for this. I’m in this video since I keep on exploring. I’ll take this out and see if it works just with that. Seems like it does, I just need these three statements right here, the first one that I had was redundant. The reason why I am able to take it out is that we already have the running total, I was actually building the running total again with the first statement.

I hope you got something out of this, we got R working with this fancy graph here and now we are probably going to move into where we can tailor this a little bit better.