Create Net Income table

[newhopepdx]

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

[MahyarTF]

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)

[newhopepdx]

Tried 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-jingzhang · Accepted solution]

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.

[newhopepdx]

Makes sense! Thanks

[parry2k]

@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.