many-to-many relationship and table totals

[Evgenia]

Hive mind,

I am running into an interesting problem when dealing with many-to-many relationship and table totals. It is killing me to know what I am doing wrong, I've spent hours trying to figure out out. Since so many of you are better at this than me, I thought I'd try here. 

I have the following tables: dimEmployees and Revenue Detail time entries

I am calculating their total hours and revenue, separately for each period when they worked for the team (this employee had multiple transfers).

Additionally - there is a revenue entry for 3/31/2019. This should not be included at all because duing this time the employee was not a part of the team.

Below is an example of the results. First using implicit measures, in which case Power BI sums up everything for all three time periods, which is nto what I want. In the last two columns I am using calculated measures. They work for the separate timeframes, but the totals don't add up. 

The code for the calculated revenue is as follows:

Calculated Revenue =

CALCULATE(

IF(

HASONEFILTER(Employees[EmployeeID]),

SUMX (

FILTER (

RevenueDetail,

(RevenueDetail[Date] <= CALCULATE ( MAX (Employees[TransferDate]) )

&& RevenueDetail[Date] >= CALCULATE ( MAX (Employees[HireDate]) ))

),

RevenueDetail[Revenue]),

SUMX (

FILTER (

RevenueDetail,

(RevenueDetail[Date] <= CALCULATE ( MAX (Employees[TransferDate]) )

&& RevenueDetail[Date] >= CALCULATE ( MIN( (Employees[HireDate]) ))

)),

RevenueDetail[Revenue])))

How can this be done better? 

I have attached a pbix with the tables and calculated measures. PBIX 

[mwegener · Accepted solution]

Hi @Evgenia 

try this.

Calculated Revenue =
SUMX (
    Employees;
    SUMX (
        FILTER (
            RevenueDetail;
            (
                RevenueDetail[Date] <= CALCULATE ( MAX ( Employees[TransferDate] ) )
                    && RevenueDetail[Date] >= CALCULATE ( MAX ( Employees[HireDate] ) )
            )
        );
        RevenueDetail[Revenue]
    )
)

If I answered your question, please mark my post as solution, this will also help others.

Please give Kudos for support.

[Evgenia]

Thank you so much @mwegener !
I had no idea SUMX can be used like this across multiple tables. So much to learn!