It’s pretty tough to get GP’s historical reports into Power BI. If, for example, you want to analyze receivables as of the end of last month using Power BI, it can be a real pain to get at the data.
Reports like the Payables or Receivables Historical Aged Trial Balance for example or the Historical Inventory Trial Balance use temporary tables in GP and are really hard to reproduce outside the application. The SSRS versions of these reports included with Dynamics GP, use stored procedures, but Power BI doesn’t support stored procedures. There are workarounds, but with the complexity of running the procedures with the right parameters and the challenge of making that data available for Power BI, it’s far from simple.
The newest version of Historical Excel Reporting for GP has a streamlined Raw Data tab making it incredibly easy to bring historical data into Power BI.
With one of the Historical Excel Reports, simply open, refresh, and save the Excel report so it has the latest data. Then, in Power BI select that Excel document as a source and pick the Raw Data tab.
Now the data is available to build visualizations in Power BI. Next time simply refresh and save the Excel sheet and refresh Power BI.
For more info on Historical Excel Reporting, visit the Historical Excel Reporting for GP page.