Calculate cashflow by including previous months balance

[cworkman2015]

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

[AllisonKennedy]

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?

[cworkman2015]

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

[AllisonKennedy]

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?

[cworkman2015]

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

[stevedep]

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. 

[cworkman2015]

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

[stevedep]

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

[cworkman2015]

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. 

[stevedep]

Can you share your code?

[cworkman2015]

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