How to properly create a result based on multiple conditions

[Jonathan_Beaton]

Apologies if this doesn't make clear sense or if it seems confusing, even I am having a bit of trouble wrapping my head around the challenge.

I am working on building an accounting system.

I have two different accounts I would like to generate reports for: "Work" and "Personal".

In my Invoices table I have columns: Account, Year, Invoice #, Invoice Total.

In my Reporting table I have a column that lets me select the account and then creates a sum of all the invoice totals for that account. To do this, I am using the following formula:

IFS(
([Account] = "Work"), SUM(SELECT(Invoices[Total], (([Row ID].[Account].[Title]) = "Work"))),
([Account] = "Personal"), SUM(SELECT(Invoices[Total], (([Row ID].[Account].[Title]) = "Personal"))),
)

This works well, but now I want to add another set of conditions that allows me to also choose the year.

To avoid over-complicating the solution, I was trying to simplify my expression and replace the last text value in the Select expression with a text value from the Reporting column "Account". Something like this:

SUM(SELECT(Bills[Line Amount], (([Row ID].[Account].[Title]) = TEXT([Account]))))

However, I think that by doing this I am actually referencing the "Account" column from the Bills table instead of the Reporting table. Any idea how to do that?

Note: The reason I am using this type of expression "[Row ID].[Account].[Title]" in the Select expression is because I actually have a list of accounts more than just "Personal" and "Business", but I wanted to simplify the question a bit. 

[WillowMobileSys]

---

@Jonathan_Beaton wrote:

This works well, but now I want to add another set of conditions that allows me to also choose the year.

---

The row filtering criteria allows for complex conditions with the use of logical operators.  You can do something like:

SUM(SELECT(Bills[Line Amount], 
           AND([Row ID].[Account].[Title]) = TEXT([Account],
                [Year] = [_THISROW].[Year]
           )
    )
)

Note:  I simplified removing unneeded parenthesis and I am assuming you have a [Year] column to properly organize the Bills rows.

I hope this helps!

[Jonathan_Beaton]

Thank you so much for your help and my apologies for my delayed response. I will give this a shot and let you know how it goes!