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:
- Starting cash= the total ytd of (sum of disbursements – sum of receipts) until the previous month
- Receipts = sum of Receipts at the current month
- Disbursements = sum of Disbursements at the current month
- 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:
- 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 - Activities[Sum of Value](‘Group'[group] = “Receipts”)
values from group receipts in the current month (current column context) - Activities[Sum of Value](‘Group'[group] = “Disbursements”)
values from group disbursements in the current month (current column context) - 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])
Way to go Kasper 🙂
Keep ’em coming!
Great article, PowerPivot can’t hope for better documentation than your blog posts 🙂
There might be a way to format measure formula for better readability, something 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]
)
Oh well, WordPress stripped all whitespace from my comment
I was thinking of this: http://i.imgur.com/25fCY.png
@Sergey Volegov
Thanks, i made it somewhat better now i think using a addin to wordpress
Kasper,
This is exactly what I meant! Very nice. I’ll try it out.
Thanks,
Greg
I tried to download your file Kasper but got a message that it had been moved … and I couldn’t get it! Any advice?
wow that is from an old post 🙂 I fixed the link!
Dear Kasper,
Could you send me the sample file as your link drive is not working.
my email: esuyheng@gmail.com
Much appreciate for your help
try this: https://1drv.ms/x/s!AhpYdMxZBU5_gS6qtnyxCMdTeR9n?e=gEAdTq
Dear Kasper,
I am really inspired by your method but I have one problem with your formula that
I want the continuous opening cash year over year without restart every year.
I don’t know how to do it. Could you help to amend formula for me.
Thanks for you help and great post!
Regards,
Heng
=IF(COUNTROWS(VALUES( CF[CashFlow])) =1,
IF( VALUES(CF[CashFlow]) = “1 Starting Cash”, TOTALYTD([CFBal](CoA[Group] = “1 Sources of Fund”) + [CFBal](CoA[Group] = “2 Project Cost”),DATEADD(‘Calendar'[Date],-1,MONTH)) ,
IF( VALUES(CF[CashFlow]) = “2 Sources of Fund”, [CFBal](CoA[Group]= “1 Sources of Fund”),
IF( VALUES(CF[CashFlow]) = “3 Project Cost”, [CFBal](‘CoA'[Group] = “2 Project Cost”),
IF( VALUES(CF[CashFlow]) = “4 Ending Cash”, TOTALYTD([CFBal](CoA[Group] = “1 Sources of Fund”) + [CFBal](CoA[Group] = “2 Project Cost”),’Calendar'[Date]) ,
BLANK())))), [CFBal])