Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Copying DAX from this post? Click here for a hack to quickly replace it with your own table names
Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C
I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com
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
Copying DAX from this post? Click here for a hack to quickly replace it with your own table names
Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C
I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com
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
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.
Proud to be a Super User!
Awesome Keyboard Shortcusts in Power BI, thumbs up if you like the article
My Community Blog Articles (check them out!)
My Blog - Power M code to automatically detect column types -
How to create test data using DAX!
Dus you miss a comma or a bracket? Prob. Sine you get a true / false output.
Proud to be a Super User!
Awesome Keyboard Shortcusts in Power BI, thumbs up if you like the article
My Community Blog Articles (check them out!)
My Blog - Power M code to automatically detect column types -
How to create test data using DAX!
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.
Can you share your code?
Proud to be a Super User!
Awesome Keyboard Shortcusts in Power BI, thumbs up if you like the article
My Community Blog Articles (check them out!)
My Blog - Power M code to automatically detect column types -
How to create test data using DAX!
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
49 | |
26 | |
21 | |
15 | |
12 |
User | Count |
---|---|
57 | |
49 | |
44 | |
19 | |
18 |