Forum Discussion
Resolver ICreate Net Income table
I have a table of Income and one of expenses. I'm wanting to create a calculated table with 3 columns: Last day of the month, Income total for that month and expense total for that month.
The goal is to have something I can use to show changes in net income over time in a graph. If there's a simpler way than creating a calculated table, I open to that.
Thanks
Hi newhopepdx
That code is for a calculated table rather than a measure. Try it to create a calculated table and see if it can meet your need.
Otherwise, you don't need to create a calculated table. Since you have two separate tables for incomes and expenses, you can create relationships between Calendar table and the separate tables on Date columns:
Calendar (one side) --(single direction)--> Exp_Current (many side)
Calendar (one side) --(single direction)--> Inc_Current (many side)
Then create three measures:
Total Income = SUM('Inc_Current'[Amount])
Total Expenses = SUM('Exp_Current'[Amount])
Net Income = [Total Income] - [Total Expenses]
Then add the EndofMonths column and above three measures into the same visual to view the result.
Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.
5 Replies
- parry2k
Super User
newhopepdx share sample data in a table format with the expected output, you don't need to create a separate table but with the measures, it can be achieved.
✨ Follow us on LinkedIn and to our YouTube channel
I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make effort to give Kudos to whoever helped to solve your problem. It is a token of appreciation!
⚡ Visit us at https://perytus.com, your one-stop shop for Power BI-related projects/training/consultancy.
MahyarTFMemorable Member
Hi,
Not sure, but maybe the below solution will be useful for you :
- I create a column in my Date Dim as below :
EndofMonthMs = ENDOFMONTH('Date'[Date])-Then I create a Measure Table and use it to develop visuals as below :* All the value for the right side visual are comes from Measure Table (Summ)
newhopepdxTried to replicate your solution. Added and EndofMonths column to my date table (Calendar) and created the measure. My report has two separate tables, one holding income records (Inc_Current) and the other expenses (Exp_Current). Here's the measure and the error I got.
v-jingzhangCommunity Support
Hi newhopepdx
That code is for a calculated table rather than a measure. Try it to create a calculated table and see if it can meet your need.
Otherwise, you don't need to create a calculated table. Since you have two separate tables for incomes and expenses, you can create relationships between Calendar table and the separate tables on Date columns:
Calendar (one side) --(single direction)--> Exp_Current (many side)
Calendar (one side) --(single direction)--> Inc_Current (many side)
Then create three measures:
Total Income = SUM('Inc_Current'[Amount])
Total Expenses = SUM('Exp_Current'[Amount])
Net Income = [Total Income] - [Total Expenses]
Then add the EndofMonths column and above three measures into the same visual to view the result.
Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.
