Power BI Cash Flow Forecast in Microsoft D365 Business Central

Business central and Power BI. We do a lot of things in Power BI, I'm going to keep going on that. I'm logged here at the Business Central, I was actually doing a session on cash flow forecast in NAVUG Focus, if you haven't been to NAVUG Focus, I highly recommend that great sessions every year around this time of year.

And we were going through cash flow forecasting 2018, but I have mentioned this before in a video, we didn’t have in business central the cash flow forecast graph, as you can see right here above my head it is here now. I swear to God I did not see this before and I think it was just added by Microsoft, I guess this is something that you can expect with continuous upgrades, now we actually have a cash flow forecast chart like we use to have and the finance performance chart, I think this is just new, it came just out of the oven, which is fantastic.

Cash flow forecast Graph
But what I wanted to do is actually create a cash flow forecast not chart, but like a graph inside Power BI and I'm just going to show you the raw mechanics of that. I did go through this also in Navug. So it's going to be this line that is growing with the changes in cash flow. What we want to do is expose if I go into web services, expose the cash flow entries and I've already done that.

If you go here, its somewhere way up here, so if I go into right here you can see that CFFE is cash Flow Forecast Entries, I've done videos on cash flow and basically the cash flow forecast you go into worksheet, you suggest a works sheet and you post that, out of that you get cash flow forecast entries, which are basically the transactions behind the forecast and I want to grab these transactions and bring them into power BI.

What I want to do is then model that, and I've enabled this and inside Power BI I just went in to get data picked the business central as the source selected the CFFE and then it came here, so if you are unsure on how that works, there are previous videos that I have actually gone through that. I'm just going to skip that because sometimes it takes a little time to actually load up the table.

If I click here in Data, now I have the data for cash flow entries right here, and what I can do is go in here and create a line graph a line char so it’s a line chart visualization and I want to put the amount as the values like so, and I want to put the cash flow date as the axis. And I actually have to set this like that so, interesting enough, when I actually drop in the cash flow date here, it came up with this date hierarchy setting and then everything just gets put into one point, 2018, and I think it's because the first part of it is a year and so it just picks the year, and everything is happening in 2018 so there is just one point.

If you change it by dropping down here the cash flow date so is not date hierarchy, you actually get by date all the changes that are happening. That is of course better. Now, this is a little bit broken because it only shows the net change in cash and normally when we look at cash flow forecast I don’t just want to see the net change, I want to see it accumulating, you know or going down.

Quick measure
Depending on whether the net change is negative or positive. In this case, I think we have all positive changes, so it's going to be going up but right now it's just showing the exact change right? so how do we do that? We can actually create this in Power BI and I think this is a really important part of Power BI. It's called quick measure. If I actually click on quick measure or new quick measure, it shows me this list over here and I can select the calculation. And here I can create fields that are measures that are often used.

So, for example, we have a running total which is exactly what we need, we need a running total by date of the amount, so we can have that incrementing by date. If I grab that, it asks me ok, what is the value field that you want? I want the amount and then, what is it running by? that’s by date. If I put that in and hit ok, then we have this amount field here called amount LCUI running tool.

Right and if I add that to my lines so I'm going to show that as well up into values, let me put it in the right place. You can see now, we have the running total, so it starts here and then it goes small up, you can see the net change goes a little bit above zero, actually, here it's below zero so it goes down, then up again and again down and then up. This is the running total up here. Which, is more interesting than the net change.

This is pretty important, now you have a running total of the cash flow and what we want to add on top of that, guess for fun, is a slicer so I'm going to put that in here and I actually want to put a date slicer into here and I did that before, so this should be nothing out of the ordinary for people who've been watching. I put cash flow date here, now I can actually look at a particular period. Now its three days etc. If I compare this; go back into business central; to this chart over here, obviously this has more to it, like these blocks so we could probably recreate that. But like the dates, I couldn't scroll the dates back and forth a little bit cumbersome, if I can only put the period length here, I can't say start date, end date, etc.

So, this is much more static vs this graph here that we can still enhance even more. If you want the columns to be showing, it can probably show that, because there are lines and columns like here. And stacked column chart which could get into. So, this is kind of a quick way of creating cash flow forecast in Power BI, obviously, I can publish that to the dashboard, but it's truly nice that Microsoft did add this over here and this finance performance that like I said, it wasn’t there before.

Hey! Things are being published as we go, that's exactly what we want, continuous upgrades.