Building a cash flow statement in PowerPivot using dynamic measures in DAX

I got an excelent question today at my new “Ask a question page”. Greg asked me if i knew of a sample for a cash flow statement in PowerPivot. I did not know of one, and to be honest i didn’t even know what a cash flow statement was :).

Searching for a sample i found this picture of a cash flow statement:

This made things more clear. We want to see income and expense in two different tables. And then in a new table the cash flow statement where we can see the starting cash at the start of a month, income and expense in the month and the ending cash with the values at the end of the month.

I got really excited to solve this using PowerPivot, so I decided to build a sample myself. First i had to create a fact table that contains the values of the income and expense, i used the picture from above to create sample data:

As you can see we have income and expense for days in three months. To determine if a specific activity is income or expense, i created a secondary table that groups my activities:

We can create a relationship between the two tables so we can put them in a table and calculate a measure with income – expense.

Next i created a separate related time table to use in time intel. functions:

We create relationships between the date from the facttable and the new date table

Now we can create the first table putting income and expense in one table per month using only the relationships between the tables and no DAX:

Now for the interesting part, how do we create the cash flow table.

What we really need is a way to create 4 different calculations per row for each month on column, as we can see in our sample we need to have the following measures:

1. Starting cash= the total ytd of (sum of disbursements – sum of receipts) until the previous month
2. Receipts = sum of Receipts at the current month
3. Disbursements = sum of Disbursements at the current month
4. Ending cash = the total ytd of (sum of disbursements – sum of receipts) until the current month

These measure will translate to dax as the following:

1. TOTALYTD(Activities[Sum of Value](‘Group'[group] = “Receipts”) – Activities[Sum of Value](‘Group'[group] = “Disbursements”),DATEADD(‘Date'[Date],-1,MONTH))
   subtract values from group disbursements from values of the group receipts for the totalytd until the previous month
2. Activities[Sum of Value](‘Group'[group] = “Receipts”)
   values from group  receipts in the current month (current column context)
3. Activities[Sum of Value](‘Group'[group] = “Disbursements”)
   values from group disbursements in the current month (current column context)
4. TOTALYTD(Activities[Sum of Value](‘Group'[group] = “Receipts”) – Activities[Sum of Value](‘Group'[group] = “Disbursements”),’Date'[Date])
   subtract values from group disbursements from group  receipts for the totalytd until the end of the current month

But how can we do this in PowerPivot ? We need to determine what to calculate per row .. to do this we can use dynamic measures in DAX, as in this excellent post from MSFT Howie Dickerman.

First we need to be able to put values on the rows on which we can base our measures, to do this i created a new table in excel with rows and loaded it into PowerPivot:

We now can put these row labels against months in a new pivottable:

Now we can create a measure which uses the current row context to determine what to calculate at the measure. When we use the VALUES function we can see what the current row context for our Cashflow[CashFlow]  is and use this in an IF  statement in the measure to calculate a different measure for each row.

The formula will look like:

= IF(COUNTROWS(VALUES( Cashflow[CashFlow])) =1,
IF( VALUES(Cashflow[CashFlow]) = "1 Starting Cash",  TOTALYTD(Activities[Sum of Value]('Group'[group] = "Receipts") - Activities[Sum of Value]('Group'[group] = "Disbursements"),DATEADD('Date'[Date],-1,MONTH)) ,
IF( VALUES(Cashflow[CashFlow]) = "2 Receipts",  Activities[Sum of Value]('Group'[group] = "Receipts"),
IF( VALUES(Cashflow[CashFlow]) = "3 Disbursements",  Activities[Sum of Value]('Group'[group] = "Disbursements"),
IF( VALUES(Cashflow[CashFlow]) = "4 Ending Cash", TOTALYTD(Activities[Sum of Value]('Group'[group] = "Receipts") - Activities[Sum of Value]('Group'[group] = "Disbursements"),'Date'[Date]) ,
BLANK())))), Activities[Sum of Value])