WOW! A Dynamics GP multi-company Financial Dashboard – Part Deux

Back in October of 2015, I posted a blog about how to modify Jared Hall’s awesome Excel Financial Dashboard that you can download here. 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 to view each company’s dashboard. 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 using Power BI over Excel is that we can make is available through the Power BI service and via the iOS and Android Apps. Making it a much better option 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

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 screen shot below after you import the data.

clip_image002

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:

  1. Header Order
  2. HeaderCalcType
  3. Dollars
  4. Dollars with Sign
  5. Dollars with Report Sign
  6. Running Dollars with Sign
  7. Current Period
  8. 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.

clip_image004

Conclusion

I’m loving the Power BI Service and all the modern cloud-based analytics that it can bring to your dashboards and reports.

Advertisements

Power BI – New Features and Need for Big Data Functionality

With January’s Power BI update, two items that I was looking forward to have been solved. The first one was the public preview of email subscriptions for report pages. The new feature allows Power BI Pro users to quickly subscribe to reports. The email will send a screen shot of the report whenever there is a change in the data with a link to the report on your Power BI web site. Detail of the new feature can be found here: https://powerbi.microsoft.com/en-us/blog/introducing-email-subscriptions-in-power-bi-stay-informed-when-it-matters/

To setup a subscription to a report, log into your Power BI site and then navigate to the report you want to subscribe to. Click on the Subscribe button, arrow 1. This will make the Subscribe to emails window available. To subscribe to this report, click on the Save and Close button, arrow 2. You can also manage all your subscriptions on this window by following the “Manage all Subscriptions” link.

Power BI Subscribe to Email

The second new feature that I like is the ability to create measures when using the DirectQuery mode. There are a set of DAX functions that are disabled by default in DirectQuery mode. To enable these in Power BI Desktop, navigate to File > Options and Settings > Options.

Power BI

In the Options Window that appears select the DirectQuery feature under Global and then add a check to the “Allow unrestricted measures in DirectQuery mode”.

Power BI

Problems

The only problem with the DAX DirectQuery options is that it doesn’t enable date hierarchy for Year, Quarter, Month, Day when you add the date field to your visual, as it should as see in the screen shot below.

Power BI

This prevents the creation of any good DAX measures and is required for handling big datasets for data handling performance and visualization speed. Your left with importing your dataset into Power BI Desktop to complete your DAX measure. To vote for this functionality, follow this to vote for it: https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/16835431-automatic-date-hierarchy-for-direct-query-data.

Conclusion

Still loving all that Power BI offers and can’t wait for what is next. .