Help with DAX formula or suggest another option

[lubosst]

Hi, I'm new to Power BI and DAX. I inherit one report and I need make some changes to it. I have two data tables:

Table1:

ID    |    Date    |    ACTIVE ORDERS

Example:

1    |    01/03/2017    |    30
1    |    01/04/2017    |    28
2    |    01/04/2017    |    12


Table2:

ID    |    Date    |    Score for closed order

Example:

1    |    01/03/2017    |    0.68
2    |    01/05/2017    |    1.2


In the Table1 I have record for every day and every ID, so  for january 2017 there will be 22 records for ID "1" and if I have 30 different ID's, there will be 30 records for every day.

In the Table2 I have record for every closed order. One ID may close more than 1 order a day or may close 0 orders.

From this source I create visual - monthly table:

ID    | AVG ACTIVE ORDERS    |    SUM OF SCORES

And use filter(slicer) to filter data by month.
In both Table1 and Table2 there are multiple records with one ID or Date, so it was quite tricky to create some relationship. I use this formula for calculating AVG ACTIVE ORDERS:

AVG ACTIVE ORDERS = CALCULATE(
                                AVERAGE(Table1[ACTIVE ORDERS]),
                                
                                FILTER(
                                        Table1,
                                        Table1[month]=MAX(Table2[month])
                                ),
        
                                FILTER(
                                        Table1,
                                        Table1[ID]=IF(
                                                        COUNTROWS(VALUES(Table2[ID]))>1,
                                                        BLANK(),
                                                        VALUES(Table2[ID])
                                                    )
                                )
                    )
                    
"month" is calculated column form Date:
month = VALUE(MONTH(Table1[DATE]) & YEAR(Table1[DATE]))

Everything works fine if I select only one month in my filter. But if I want to select several months (for quater results), I got bad results. I managed to find out, that "Table1[month]=MAX(Table2[month]" part of may formula is the reason. But somehow I'm not able to find workaround.
(It's not my formula and I'm new to Power BI and DAX)

I appreciate any help with this problem...

[v-sihou-msft]

@lubosst

In this scenario, both tables are fact table. You need to create a ID dimension table and date dimension table between them, and build relationship on ID and date. Then you can create a measure like:

AVG ACTIVE ORDERS =
CALCULATE (
    AVERAGE ( Table1[ACTIVE ORDERS] ),
    ALLEXCEPT ( Table1, Table1[ID], Table1[Month] )
)

Regards,