Power BI Dates in Microsoft D365 Business Central

Power BI Dates in Microsoft D365 Business Central

We are going to go through today is Business Central and Power BI.
Las video we did some power BI, we went into Power BI, connected some tables, we created this little screen here where we are using the item budgets to do item estimates or item sales estimates and then connecting that to actual sales and showing the difference. Right here we see the sales amount, this is the estimated sales amount and the actual sales amount.

One of the issues with this is that we have no date range on this, I can't filter on a date or a month or something like that. How do we add a filter for dates into the power BI? That turns out to be not completely obvious but once you know it, is not that hard. I know I'm kind of diving into Power BI and you say, wait a minute! this is like NAV Business Central right?, but no is not is 365 and is everything in Microsoft that connects all around, so we sort of got to get used to being in all of these applications and think they are all part of Dynamics Business Central. So, into Power BI we go.

I'm going to jump into Power BI, and I'm going to show you the result of what I did first. It's going to be a little bit different because it takes a little effort to do this, not huge, so I'm not going to be typing here and making mistakes. I'm just going to show you what I did. In here we have the same table as we had before, except they have a slice in here. A slicer is this visualization in here and it allows you to put dates, now the slices if you connect it to one of the dates like the budget entries or item ledger entries, it will only connect to one of them. You can only connect one date into the slicer. But you want all dates in 2018 with both, so this is where the issue comes in. You need to connect the slicer to a date table which you don’t have yet.

But I did that and as you can see, I can move this around here like we have no estimates in March, if I add April into it we have the estimates in here and just a cruise the sales from March to April. If I look only at April, you can see that here are the actual numbers for April. This is the sales estimate for April and this is the actual sales for April. So, this is Jiving pretty nice for the numbers of months here. I could have done dates like weeks, days, whatever it doesn't matter, I just decided to go for months.

How do I actually do this? Let's get into the details. If I go into Tables right here, I just start with looking here on the side up here, we have the budget entries, so this is the estimates. If I click on that you can see these are the estimates that we took in the last video and then we have item ledger entries, which is all the shipments, all the sales. And then we have item cards which are all the items.

We connected the item to the item budget entries and to the item ledger entries. We could just start dragging things in. What about the date? where do we get the date?, well it turns out we sort of have a table of dates in Business Central and if you connect to that or a page that has a Matrix built with dates, it will just load endlessly. Because it's just fetching endless dates because it has endless dates. We don’t want that, what do we do? we actually create the dates inside power BI. We can create a table in Power BI. It's up here in modeling, you can just say new table, that what I did, I created a new table.

Create a date table
I created a table called date table, is using this tax statement called calendar and I put in the start date which is the beginning of the year 2018 and the end date which is the end of the year 2018. This is my date, you could do whatever date you like here it doesn’t matter. There is a version of this that automatically builds the date range based on the data that you are working with I didn’t get into that, but you can test that around. It's kind of taking into the next level.

Right here I have a calendar date table, you can see I have all the dates here. And I created two extra columns, I created month which is just grabbing the month part of the date, and then the week number, so I figured it might be able to filter weeks two, who knows. We have that in, I also created another table. Since the date table has all the dates and it's grabbing out the month number in month column, you'll have the number one a lot of times for January 31 times. You need to group that up for months and I wanted the months to have a name. There might be a simple way of doing this, but I was just curious about creating tables on Power BI.

I created this data table, it allows me to say, ok, what columns do you have? and what is the data in those columns? Data is just given here as a set of tuples with the data inside of it. So, I have here the month number and the date of the month all the way through. Now, what do I do with this? I go into the relationships here and I connect the dates table to the item budget entries and to the item ledger entries. Now that they are connected, I grab all the dates which is great! now I'm not connecting the slices to the item budget entries or the item ledger entries.

I'm connecting the date table and the date table has all of that. Even better I could group up the date table into month number which is my new table here and I connect the slicer to the months. Which, is connected to the date table which is connected to item ledger entries. So pretty sweet. Now it's all connected. That gives me this.

I can connect the slicer here, you can see I'm connecting it to month number and over here I have just the standard stuff, actual sales, estimated sales and the description of the items. Now this works like a charm. I can just open this up from March 2 to May and here are the numbers. So that's how you actually connect to dates. This was 10 minutes, not bad, you are in Power BI, Business Intelligence. Not a crazy learning curve. Start using It if you haven't already!