topic Re: Calculate cashflow by including previous months balance in DAX Commands and Tips

Calculate cashflow by including previous months balance

cworkman2015 — Mon, 29 Jun 2020 18:22:14 GMT

Hello,

I'm trying to calculate a cashflow forecast by looking at the current months ending balance + income - expense.

I then need to take that value and use it as the next months ending balance. In the attached excel file you can see how this formula executes - however, i'm struggling with setting that up in PowerBI.

Here is the logic:

Any suggestions?

Thanks

Re: Calculate cashflow by including previous months balance

AllisonKennedy — Tue, 30 Jun 2020 02:46:46 GMT

What does the raw data look like? Can you please provide a sample table of the current data that will be imported into PowerBI, you have already explained your desired result well, just need to understand which columns we have to work with and how they are formatted - is the data already grouped by month?

Re: Calculate cashflow by including previous months balance

cworkman2015 — Tue, 30 Jun 2020 13:49:40 GMT

Thank you for your quick follow up - the data is daily transactions and my visual will be grouping by month, so the data needs to be grouped by month as well.

Here's the table:

Date	Balance	Income	Expense
1/1/2020	$ 25.00	$ 100.00	$ 50.00
2/1/2020	$ 32.00	$ 100.00	$ 50.00
3/1/2020	$ 45.00	$ 100.00	$ 50.00
4/1/2020	$ 12.00	$ 100.00	$ 50.00
5/1/2020	$ 32.00	$ 100.00	$ 50.00
6/1/2020	$ 50.00	$ 100.00	$ 50.00
7/1/2020		$ 125.00	$ 50.00
8/1/2020		$ 130.00	$ 50.00
9/1/2020		$ 115.00	$ 50.00
10/1/2020		$ 120.00	$ 50.00
11/1/2020		$ 125.00	$ 50.00
12/1/2020		$ 125.00	$ 50.00

Thank you

Re: Calculate cashflow by including previous months balance

AllisonKennedy — Thu, 02 Jul 2020 01:13:24 GMT

Your sample table just has one date per month, but from your description the data comes in as daily so I presume there are multiple entries for each month? Do we need to take a SUM of those entries or just look at the Balance, Income and Expense for the last day of the month?

Re: Calculate cashflow by including previous months balance

cworkman2015 — Thu, 02 Jul 2020 18:18:05 GMT

Allison,

Sorry for not being clear. There is multipe entries per month, I was minimizing for simplicity sake.

It will be referencing a measure (Balance):

Said Balance measure is this:

var endOfMonthCashEstimate = if([Last Bank Balance] > 0, [Last Bank Balance] + [Accrual Income] - [Accrual Expense], CALCULATE( SUM('Deliver CUST_TRANS_REPORT'[Amount]), 'Deliver CUST_TRANS_REPORT'[TxnType] = "Register")) return endOfMonthCashEstimate

Thank you,

Channing

Re: Calculate cashflow by including previous months balance

stevedep — Fri, 03 Jul 2020 05:41:16 GMT

Perhaps this works for you:

Code:

InMonthBalance = SUM('Table'[Income])-SUM('Table'[Expense])

PrevMonthInBalance = CALCULATE([InMonthBalance];PREVIOUSMONTH('Date'[Date]))

NetBalance = IF(ISINSCOPE('Date'[YearMonthnumber]);[InMonthBalance]-[PrevMonthInBalance])

File.

Hope it works for you, if so, pls mark as solution.

Kind regards,

Steve.

Re: Calculate cashflow by including previous months balance

cworkman2015 — Mon, 06 Jul 2020 13:16:58 GMT

Thanks for your response Steve. This isn't returning the expected results when applied to my models data (included in my last snippet) The Net Balance returns either True, or False boolean values and the previous Month in balance is just null. Thank you, Channing

Re: Calculate cashflow by including previous months balance

stevedep — Mon, 06 Jul 2020 16:56:05 GMT

Dus you miss a comma or a bracket? Prob. Sine you get a true / false output.

Re: Calculate cashflow by including previous months balance

cworkman2015 — Mon, 06 Jul 2020 18:44:44 GMT

i've copied your exact syntax and replaced the field names. Also, semi-colons return an error in my formulas so I'm unsure what to do about that, I replaced them with commas.

Re: Calculate cashflow by including previous months balance

stevedep — Mon, 06 Jul 2020 19:02:27 GMT

Can you share your code?

Re: Calculate cashflow by including previous months balance

cworkman2015 — Tue, 07 Jul 2020 13:38:34 GMT

Steve,

I fixed my NetBalance formula as it was using an = instead of a subtraction, but since the previous month formula returns NULL it is still a NULL result

Here are my formulas:

InMonthBalance = SUM('Model'[Income])-SUM('Model'[Expense])

PrevMonthInBalance = CALCULATE([InMonthBalance],PREVIOUSMONTH('Model'[Date]))

NetBalance = if(ISINSCOPE('Model'[Date]),[InMonthBalance]-[PrevMonthInBalance])

Here are my results:

Thanks,
Channing