Back in September of 2015, I posted a blog about how to modify Jared Hall’s awesome Excel Financial Dashboard that you can download. The problem with his solutions is that most Dynamics GP environments are multi-company setups making these dashboards not very functional without navigating and opening multiple Excel spreadsheets. So, I took it upon myself to modify the one provided by Jared Hall to work in a multi-company Dynamics GP setup, but that was so 2015!
This is 2017, so let’s create our Financial Dashboard in Power BI. The best part about for mobility and availability.
The Parts to the Solution
For my solution, I choose to use the following tools:
- A Small DataMart
- Power BI Designer
- Power BI Service
The DataMart
I chose to implement a DataMart to simplify the importing of the tables into the Power BI Designer. I created the following tables to implement my Financial Dashboard:
- DimAccount
- DimHeader – to summarize the report layout
- DimDate
- DimCompany
- FactFinance – General Ledger data from the GL20000 and GL30000 tables of each company
The Dashboard Design
I imported the above tables into Power BI Designer rather than using the DirectQuery mode. Using the Import option will allow for the full functionality of DAX and more importantly the Time Intelligence functions of DAX. Your table relationships should look like the screenshot below after you import the data.
The DAX Measures
The Profit & Loss Statement layout is handled by several DAX measures, the DimHeader table, and the Sign and Report Sign columns within the Account table. I created the following DAX measures in the order listed below:
- Header Order
- HeaderCalcType
- Dollars
- Dollars with Sign
- Dollars with Report Sign
- Running Dollars with Sign
- Current Period
- Cumulative Sales (Selected)
The code for the DAX measures can be downloaded here from the Finance Dashboard template.
Visualize This
An easy to use modern multi-company cloud-based or mobile app Finance Dashboard that shows your critical data, so you can spot trends, share insights, and make smart decisions.
Conclusion
I love the Power BI Service and all the modern cloud-based analytics that it can bring to your dashboards and reports. Contact KTL Solutions for this financial dashboard or to help kick start your organization into becoming data driven by exploring your Business Intelligence, BI360, Power BI, Microsoft Dynamics GP and CRM needs.
BARRY CROWELL, MBA | BI/EDW Solution Architect
Barry is a Microsoft SQL Certified Professional with a strong knowledge of the Microsoft’s BI Stack (SSIS, SSRS, SSAS and Power BI). He has architected, developed and deployed clients’ Business Intelligence needs using Microsoft’s BI Stack and/or Solver’s BI360. His solutions have included SSIS ETL tools, SSRS reports and dashboards, Excel dashboards, Power BI reports and dashboards, and SSAS cubes. He has performed implementations as the lead consultant and/or end-user project manager. Barry has over 20 years of experience working in accounting and the Microsoft Dynamics GP industry, and in various industries such as housing authorities, universities, Tribal governments, and casino hospitality. He possesses a Bachelor’s Degree in Accounting and Business Administration from Black Hills State University and a Master’s in Business Administration from La Salle University. Barry’s experience in both the IT consultant field and experience as an accountant gives him the ability to understand the issues from both the IT and Finance prospective and provide a solution that fits the needs of all parties involved.
