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

Posted by Kasper de Jonge

UPDATE FROM ROB:  You can now find all kinds of Accounting and Financial Techniques filed under a category created specifically for them.  To view those posts click here:

http://www.powerpivotpro.com/category/accountingfinancial-techniques/

Also, there is now also ANOTHER in-depth example of cash flow statements here:

http://www.powerpivotpro.com/2011/12/cash-flow-statement-in-powerpivot/ 

(Now, back to Kasper’s excellent post)

I got an excellent question at my “Ask a question page” on my blog. 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])

This will create one measure that depending on the row context shows different calculations.
Resulting the actual cashflow pivottable:
You can see that the values are calculated per cashflow type per month showing a different calculation. I hope this was what Greg meant :)
This example shows again the great power of DAX, a lot of things are possible. I still get really excited about PowerPivot.
The sample file can be downloaded from my skydrive.

6 Responses to Building a cash flow statement in PowerPivot using dynamic measures in DAX

  1. [...] You get more good advice, examples, and tips.  Kasper has a ton of great stuff to share, like the post below, and I thought it was time everyone saw more of it.  In all honesty, I learn at least as much [...]

  2. Michiel Rozema says:

    Cool measures, Kasper!
    However, your solution to get the layout right looks quite complicated. There is an easier way to do to same thing:
    1. Define the four measures individually
    2. Create a pivot table with [Month] as column labels
    3. Add the four measures as values
    4. In the PivotTable Options ribbon, select ‘Options’
    5. In the Display tab, check the ‘Classic PivotTable layout’ box (which says ‘enables dragging of fields in the grid’ – this will do the trick)
    6. Drag the cell with the word ‘Values’ to the first column of the pivot table.

    • ha cool :) Thanks Michiel. This is the second time today someone told me you can do something in excel which i solved in code. That shows the difference between Excel users and developers :)

      Ow yeah and i of course want to see what i can do with DAX :)

  3. Kellan Danielson says:

    Ok I am losing my mind trying to do a calculation that I thought would be fairly simple. If anyone can help I would be eternally grateful. Here is my dilemma:

    I am trying to build a measure that will pull the GL balance (Actuals) if the month on the column label is less than or equal to the datekey on the report filter (or it could be month, don’t care which. both are in a dimdate table linked to GL_balance and GL_Budget). And if the month label is greater than the datekey pull from the GL Budget (Budget). I thought I could use a separate non-relational dimperiod table and use a combination of if(countrows and sumx but I am just chasing my tail on this one :/

    I can go into further detail if needed but any help would be greatly appreciated!!

    Thanks,
    Kellan

    • Kellan Danielson says:

      Ok so I figured out a solution which involved the ‘using slicers in measures’ post. I created a 1-12 table called Period and used the sumx(filter to filter for the budget numbers. Then used a sumx to combine the two so I could have a proper grand total and combination. If someone else is trying to do this and needs a little more in depth information ping me and I would be glad to help.

      Thanks,

      Kellan

  4. Herbert Seidenberg says:

    Excel 2010 with PowerPivot Add-In
    Without DAX IF() formula but with DAX equivalent of “Running Total in…” formula.
    Same data base as Kasper’s.
    http://www.mediafire.com/download/cs3z53gnhvi5msw/09_09_13.xlsx

Leave a Reply