Measure evaluates data at engagement level. How can I SUM these values to get company-wide value?

[jdormer]

Looking for some assistance with a "Work Remaining" report I have built. 

Currently the report is displaying four values:

POAmount - Existing column in original dataset

AmountInvoiced - Existing column in original dataset

A measure called RevSinceInvoice = CALCULATE(
SUM('Time'[Revenue]),
FILTER('Time', 'Time'[Date]> (MAX(Invoices[Created])
)))

A measure called PORemaining = SUM(Engagement[POAmount]) - [RevSinceInvoice] - SUM(Invoices[Amount])

I have slicers on the report so I can view by client and engagement the original PO Amount, the amount that has been invoiced, work that has been performed since the last invoice (RevSinceInvoice), and the amount of work remaining to be performed (PORemaining). This all works as expected when viewing a single engagement. 

However, there is also a need to view the same information at a company level, for all clients and all engagements. When I do not make a selection in the slicers, attempting to view company wide data, the RevSinceInvoice measure is picking up a single date for MAX(Invoices[Created]), which means that only 'Time'[Revenue] entries after the most recent invoice for any client are reflected in RevSinceInvoice. What I really need is a MAX(Invoices[Created] for each client so that all 'Time'[Revenue] entries are evaluated against that client's most recent invoice, not the most recent of all invoices. 

1) I hope that makes sense. 

2) How can I aggregate the RevSinceInvoice values to get the correct company-wide figure?

Thanks.

[Sean]

what are the tables and columns involved in this Calculation?

Time Table

Revenue??? (why is Revenue in Time - I guess that's a measure that just ended up there)

Engagement Table

POAmount,

Invoices Table

Amount, Created

[jdormer]

Hi @Sean

I'm pulling information from the database that is behind the application we use for time tracking. Revenue is a column in the Time table that is calculated based on the number of hours billed x rate, showing the revenue earned for that time entry. 

There are other tables as well that I am not directly interacting with in my report:

-Billing

-Client

-Deliverable

-Fees

-Managers

-Users

-Worktypes

Does that help clarify? Let me know if there's other information I can provide that would be helpful. 

Thanks.

[Sean]

How are these 3 table releated?