In the realm of financial statements, both the income statement and the balance sheet play crucial roles in providing insight into a company’s financial health, but they serve different purposes. The income statement provides a summary of a company’s revenues, expenses, and profits (or losses) over a specific period, usually quarterly or annually. The balance sheet presents a snapshot of a company’s financial position at a specific point in time, typically at the end of a reporting period.

Therefore, converting data from a General Ledger to regular financial statements in Power BI typically involves different calculation methods for the income statement and the balance sheet due to their distinct purposes and structures.

Income Statement

To generate the income statement data for the years 2018, 2019, and 2020, as illustrated in the figure below, we simply require the synthesis of corresponding data for each year from the GL. I refer to this data segment for the period as “FTP” (for the period).

Hence, we can obtain the respective values for each year by applying: Total_FTP = SUM(tbl_GL[Amount]).

Balance Sheet

Relative to the income statement, the process of calculating values in the balance sheet is generally more intricate. This complexity arises from the balance sheet’s task of determining the total value at the end of each fiscal year, a concept I refer to as “total to day” (TTD). To illustrate, consider a straightforward example: to calculate the value for 2020, as depicted in the figure below, one must sum the values for 2018, 2019, and 2020. Similarly, to determine the value for 2019, it entails summing the values for 2019 and 2018.

Firstly, we aim to identify the minimum value within the fiscal year calendar. To ensure that external filters do not influence this minimum date, we utilize the all function to eliminate their effect.

MinDateAcross = CALCULATE(MIN(tbl_Calendar[Date]), FILTER(ALL(tbl_Calendar[Date]), tbl_Calendar[Date]))

For the maximum date, this effect does not need to be considered: MaxDate = MAX(tbl_Calendar[Date])

Finally, the required value can be obtained according to the following formula:

BalanceSheetValue = CALCULATE(SUM(tbl_GL[Amount]),tbl_ChartofAccounts[Report]=”Balance Sheet”,DATESBETWEEN(tbl_Calendar[Date].[Date],[MinDateAcross],[MaxDate]))

Flory Hou
Author: Flory Hou