155 - Power BI Cash Flow Forecast Pt. 2 in Microsoft D365 Business Central

Power BI Cash Flow Forecast Pt.2 in Microsoft D365 Business Central

 I'm going to keep going in Power BI. We are outside Business Central NAV and all of that, we are inside Power BI why? because Power BI is the reporting tool for business central to a certain extent at least the visualization of business intelligence and I had been working on cash flow analysis and in here you can actually see that we have cash flow by the due date, actually buy cash flow date from the cash flow module.

If you are jumping into these videos or video series please watch the previous videos on that, so you can see how we created this chart. But if you are new to this basically what we did is, we brought in tables from Business central into Power BI and these are web services and we connected that to this chart right here and I can see the net change by date and then I had to do a quick measure to get the running total. Which, was kind of a trick there.

What I want to do now, actually in business Central you can filter out what part is payable, what part is receivable, what part is liquid assets and I want to be able to filter that in a running total, so it should be very easy to filter that here. For example, let me show you, if I go in here and get a table; I'm actually using this cash flow account; and I show the name, I can see all the cashflow accounts that are in here, if I make that a slices then I can check off anything I want to see, so if I want to see receivables you can see that changed of here I'm just looking at receivables.

If I want to see payables, I can see that coming here. The entire graph is affected by that, but let's say I want to see the payables on this graph as well. As well as the top line here, all accumulated, I can't do that with only this because whenever I click then it just takes that, it shows me only that. It doesn’t show me, both right? there is no way to do that because I'm filtering everything. Basically, what I want to do, is instead of filtering this graph I want to create another accumulating line over here that is only looking at receivables. How do I do that? it’s a good question.

I'm going to go here into page two just to show you how the data works, click on here, put a table and in this table, I'm going to pick the cash flow date and the amount, just to show you. Here I have the amount of the cash flow date, these are all the dates. I also want to see the running total.

Notice here the running total is actually not running, why is that? it's because you have to change the cash flow date to not show date hierarchy but on the actual cash flow date. Do that and all the sudden it works right. So, it’s a very interesting trick. It's best to make note of that. At the same time if I bring in my slicer right here and I just show the name like it was before and now if I filter on that, let's say, receivables which were one of the things that I wanted to see. Now I can see how only the receivables are here, and you can see how it accumulates.

What I want to do is have a third column here, I show the amount, the running total, and I want to show the running total of only receivables in a third column here along with this column. How do I do that? I need to create another measure. We are going to go in here and say new quick measure and it says ok, select calculation and I'm going to do another running total, the same way as I did it before. Pick the amount and pick the cash flow date. Here in this quick drag and drop I can't do what I need, I need to add to the state, what this does here it builds a Dax formula for me, and now I build this This looks like code and it is code, so what I'm going to do is add to that code.

Right now its adding the amount on this filter right here, which is on dates and I want to add another filter so all I have to do is do a Comma filter and here I'm actually going to do all the selected, because all selected means that it works within the filtering on that, which is the running total and then I'm going to pick the cash flow account, which is that table which I'm actually filtering on a top table and then I pick the name column. So, now I have a new quick measure here, which is receivables, If I actually click on this here, and I am going to add it right here, it should get it over here. Now I have a running total which is a total, and then I have a running total which should be the receivables.

How can I make sure that this is the receivables? All I must do is filter here on receivables and it should match up, and it does you can see that the running total filter matches the receivables. Now it works, that great! I can just add this to this table here, I can just click on this and now I have the red line here, which is showing me the receivables. Just the receivable part. And then you can, of course, do this for payables, for liquid assets or liquid funds and other things, Power BI! Cool!