CHALLENGES, CONSTRAINTS AND PITFALLS OF USING POWER BI IN A FINANCIAL REPORTING CONTEXT

Microsoft Power BI is a powerful tool that allows you to deploy advanced visualizations securely within the organization. It is already a must-have for monitoring sales, marketing, and production processes. However, it is still much less present in the operation of Finance groups, particularly at the level of purely financial reports. We explore why and explain the reasons why this is so.

Words: 815 Reading time: 4 minutes 4 seconds

Challenges

Microsoft Power BI is a hot topic among all our customers. The power of the tool offers business lines the promise of finally being able to be autonomous in the preparation of the organization's reports. Finance groups are not immune to this craze and many finally see it as a way to reduce the famous dependence on "Spreadmart" in Excel which causes so many governance issues. However, this long-sought dream is strewn with pitfalls which leave a bitter taste for those who embarked on this adventure without preparation.

First, financial reports are fundamentally different from the usual reports found in analytics such as sales reports. One of the main challenges is that financial reports require greater knowledge of the DAX language, which is not easy to master, especially for non-technical people. This translates into a series of challenges that become relatively complex for CPAs accustomed to the much greater flexibility/simplicity of Microsoft Excel, such as:

1 - Management of tax calendars

Many companies have fiscal calendars that are not aligned with calendar months. For example, a 35-day tax period that begins on February 25 and ends on April 3, or a 13th period at the end of the year. Power BI date functions cannot be used to determine relative periods (like previous period, same period but for previous fiscal year), and must be programmed in DAX with appropriate data structures.

2 - Heterogeneous cross-column measurements

A large majority of reports require cross-referencing heterogeneous measurements, each of which must be programmed in DAX language. For example, reports with Actual, Budget, Variance, Variance in % in columns are easy to produce in Excel but much more complex in Power BI.

3 - Sign reversal

Certain values, as well as their consolidated values, are stored as negative in the GL module, but must be displayed as positive in certain reports such as the income statement. Again, DAX is required, as well as pre-designed data structures for this.

4 - Non-regular hierarchies

The display of non-regular consolidation hierarchies, whose "branches" can be of different levels, and whose accounts can be attached to them at any level. Matrix visualization cannot handle this case well, and this once again requires data structures designed in advance.

5 - Aggregation with totals at the bottom

The matrix visualization does not allow you to display the totals of the consolidation levels at the bottom as is the case in the financial reports of the ERP GL modules.

6 - Multilingual and short/long aliases

Effective management of the multilingual aspect (a single report whose data, field names and texts are automatically translated according to the user's preferences) still remains a major challenge. Although this is not a challenge only for Finance, it is made even more complex by the needs to rename account names and totals.

Solution

The introduction of Microsoft Power BI in Finance requires greater technical knowledge than for other business lines. To avoid wasting time, this additional complexity should be handled by Power BI specialists rather than financial analysts. Another solution is to purchase reports or data sets already assembled, where these issues are already mitigated.

Conclusion

We have shown 6 important challenges for Finance groups accustomed to Microsoft Excel when migrating their reporting ecosystem to Microsoft Power BI. We have been implementing financial analytics solutions for over 20 years, and have a specialized solution to address these issues, and many others, with less effort and human error.

  Learn about Swift Finance