How to Get Project KPIs from D365 into Trusted Power BI Reports

This post talks about a common problem: your Dynamics 365 F&O data is all mixed up, so nobody trusts your Power BI reports. We will show you a simple, two-step solution. First, you have to fix the data itself. Second, you build the reports and help your team learn. This is a guide to stop using manual Excel files and start using project dashboards you can believe in. 

So, you have all this data sitting in your Dynamics 365 F&O system. But you are still exporting files to Excel every single week just to make a report. Does that sound right?

Well then, the real fix isn’t just “go get Power BI.” It is, actually, a two-step plan. You must make your data trustworthy first.

Why do your reports keep failing, even when you have D365 F&O and Power BI? 

You have this very strong D365 F&O system. It is supposed to be your one place for all the correct information. But, actually, you probably still feel like your teams are not on the same page. They are working in different “silos.” 

For many companies that run projects, the way they work now involves a lot of manual steps. You find yourself grabbing bits of information from different parts of the system. Then you export it all to Excel. And then you have to use a lot of complicated formulas just to make the numbers useful. This takes up so much time. It also makes you worry if the reports are even correct or if they could pass an audit. 

Maybe you have even tried to use Power BI a little bit. But nobody trusts the final reports. 

You see, this happens because the data tables underneath are just not working right. And if the foundation is bad, it really doesn’t matter what is Power BI or how nice the report looks. Nobody is going to use it. 

What is the two-step plan that actually works? 

The real solution is to stop thinking about the final report for a minute. You must fix the data first. With a simple, two-step plan. 

Step 1: Build a Strong Data Foundation 

This is the most important step. It is what makes everything else possible. This step is all about cleaning up those data tables from D365 F&O. It means you fix the connections (relationships) between all of those tables. It also means you make sure your date-filtering works, so you can actually sort by week or by month. You are building one, central “semantic model” that is trustworthy. A good Power BI dashboard can then connect to this model and work right every time. 

Step 2: Build the Reports and Help Your Team 

Now you move on to building reports after that data foundation is strong. You start with the most important dashboards. The ones people need most. But you don’t just get reports built for you. A really big part of this step is training your own internal “super users.” These are the people who already know your business very well. You teach them how to use the new, clean data model. Then they can build new report pages or new measures themselves. This is how you learn to do it on your own. 

What specific data problems should you fix first? 

You are probably seeing some very annoying, specific problems. These are very common for companies that run projects. These industries often have needs that are a lot like business intelligence for manufacturing. Here are the problems we see all the time, and the simple ways to fix them. 

• The Problem: Your Purchase Order (PO) numbers just do not match up. The “real” PO number your business uses to bill clients is in one table. But a different, internal PO number is in another table. You can’t make them connect.
  The Fix: So, you have to create something called a “conformed key” or a “bridge table.” This is just a new, clean table. Its only job is to logically link the “real” PO number to all the other data. This lets your reports finally line up correctly. 
• The Problem: You cannot trust the date filters. You just want to see the data for “last week” or “this month.” But it never, ever works correctly.
  The Fix: You need to add a proper, separate “Date dimension” table. This is a normal, standard way to fix this. This special table connects to your data. And it lets you sort and filter by any date, week, month, or year. And it will be reliable. 
• The Problem: All of your important business rules are stuck in Excel. Your big weekly report only works because of a giant XLOOKUP or VLOOKUP formula inside a spreadsheet.
  The Fix: You have to move that logic. It must come out of Excel. You move it into Power Query or DAX (which is the language inside the Power BI tool). When that logic is in one central place, inside the data model, it can be audited. It can be refreshed automatically. And it is the same for everyone. 

What reports should you build first? 

So, once your data is finally clean, you can build the reports your teams have wanted for so long. You can stop looking at pictures of Power BI dashboard examples. You can build your own tools that actually work. 

For a company that runs on projects, the most important reports are usually very clear. 

1. A Weekly Budget Line Report: This one is for your project managers. It must show them, very clearly, the budget versus what was actually spent. And it has to show what is left. Plus, it must include those correct PO numbers for billing. 
2. A Procurement Report: This report needs to show every single PO line for every single project. And it has to use that corrected PO mapping you built in Step 1. 
3. Project Management KPIs: This is the high-level dashboard for managers. It needs to track the big numbers, like billable hours per week, revenue per week, cost variance, and how busy your teams are (utilization). 

How should you think about licenses and sharing? 

This part can feel a little confusing. But in simple terms, you have “Pro” and “Premium.” 

So, a Pro license is usually for your “builders.” These are your “super users” and the other people who will create, publish, and work with the reports.

Premium (which comes as “per user” or “per capacity”) is often better for very large groups of people. If you have many “viewers” who just need to look at the information, Premium per capacity is often a good choice. It also gives you some advanced Power BI features to use.

Now, here is one very important point. You must not use the “Publish to web” or public embed feature. Your project data, your financials, and your costs are all sensitive. That “Publish to web” feature actually makes your data public on the internet for anyone to find. You must use the secure ways to share reports inside your company. 

How do teams actually get a project like this done? 

A big project can seem scary. But you do not have to do it all at once. Actually, a step-by-step plan is much, much smarter. You can set clear goals. For example, you could plan to have the Step 1 data model cleanup finished by the middle of summer. Then, you can plan to have the first, most important reports running by September. The rest can be finished in the fall. 

This step-by-step thinking also helps with your budget. Instead of one giant, complex project, you can start with a smaller, clearly defined job. You can focus the first budget just on Step 1 (the data model) and maybe one or two key reports. This lets you get a “win” very quickly. It proves that this works. It also helps you get a real idea of the Power BI consulting cost before you promise to do a huge project. If you feel stuck, this is often a good time to get expert Power BI consulting to help plan that first, small-scope project. 

How do you make sure this keeps working in the future? 

The main goal is to stop the problem of having spreadsheets everywhere. You make this new solution last by having good rules, or “governance.” 

Your business logic (all those old XLOOKUPs) is now in one central place, in Power Query. Your data model has a clear owner. The data refresh is automatic and repeatable. No more manual exporting. 

And, you have your new “super users.” This is really one of the biggest Power BI benefits. Because you trained them on the clean data model, they can add new report pages. They can add new calculations. And they can do it without needing a whole new project every time. This is how your company will grow. 

What does “good” or “done” actually look like? 

So, how do you know when you are finished and you have won? “Done” is not just getting a report. “Done” is a whole new, better way of working. 

It means your project managers get weekly numbers they can finally trust. They can click into a project view and see all the details. And, most important of all, it means you are doing almost zero manual Excel work to get those numbers. 

You can even use a short checklist to be sure: Do the dates filter right? Yes. Do the PO numbers finally match up? Yes. Does the data refresh every morning, all by itself? Yes. When you can say “yes” to these, you have a system that can even help you look into the future. It can help with business forecasting with Power BI, not just looking at what already happened.