The cash flow statement is just one of the three financial statements. Investopedia defined the cash flow statement (CFS) as a financial statement that summarizes the movement of cash and cash equivalents (CCE) that come in and go out of a company.
This is pretty straightforward compared to the income statement and balance sheet created in PowerBI.
If you need to follow, you can download the data used here—tables required for the cash flow report includes.
It is necessary to unpivot the data as it is unsuitable for the existing model.
The transformed data then looks somewhat like this.
Remember, the goal was to build a single report comprising all three financial statements, not three standalone reports. The overall model looks somewhat below.
The model diagram shows no established relationship between the calendar table and the cash flow data table. Interesting? How do we establish a relationship since the cash flow data is on an annual basis? Using the year column will create a many-many relationship. Not what we are interested in—what a headache.
The solution to this headache is to use the TREATAS function.
The TREATAS function creates a virtual relationship between the tables specified.
A quick peek at the cash flow data reveals a column called the cash flow sub-category. Hence we need to write a DAX measure that calculates the cash value for each category.
VALUES (‘Calendar’ [Year]) is used because unique values are required. With the aid of the VALUES function, we have established some virtual 1-many relationships between the Year column on the calendar table and the year column on the cash flow data.
From the cashflow template imported into PowerBI, the cashflow sub-category has been circled in blue. The DAX created above will calculate the itemized cash balances for each of them.
Row 4–8, 13–15, 17–19, 23–25, and 27–29 will serve as filter context for the CS Values measure created. If you are unclear about how filter context works, I have explained that here.
Since the itemized rows in the cash flow statement are sorted, next on the agenda are the totals and sub-totals, and these will each need their different measures, which will then be encompassed into a single DAX measure.
The following measures are used to calculate the totals and sub-totals.
Cash receipt from — operations
2. Cash receipt from — investing
3. Cash receipt from — financing
4. Cash paid for — operations
Note that this was multiplied by -1 because, in the data provided, the values were recorded with a positive sign. In an accounting statement, outflows are registered with a negative sign.
5. Cash paid for — investing
6. Cash paid for — financing
The DAX measure below encompasses all sub-totals and totals.
All left is adding the necessary dimensions and dax measure onto a table.
We have completed the cash flow statement.
You can be more productive at work by developing these financial statements in PowerBI and setting up a refresh schedule.
Adios
