Moving average from reference in another table

[bklyn3]

Hello

I have a table1 with a columns Date and a column Hours

I calculate a moving average with DAX using the formula at the bottom of the post. 

The formula works and it gaves a rolling average.

Table 1:

In July I have 23,039 and a moving average of 15,220 which is correct. 

Now, I have second table2 with a column date  and a common key with the first table (Table2 has many to one to the Table1).

Based on that key, I am trying to bring the column "hours" and "rolling average"  to the second table".

When I do so, the rolling average get re-calculated based on the dates in Table2 instead of showing me what was in Table 1

So in this case if I only look at july (see picture below), the column hours is correct (23,039) but the moving average is not 15,220 (which is what I want) but instead powerBI recalculates the moving average for that table (so the result is 23,039 also )

How can I have the rolling average showing in the table 2 without the value changing. That is the rolling average based on the values of the first table. 

Thank you

Table 2

Thanks

Patrick

DAX Script I am using for the moving average 

Hours rolling average =

IF(

    ISFILTERED('Table1'[Date]),

    ERROR("Time intelligence quick measures can only be grouped or filtered by the Power BI-provided date hierarchy or primary date column."),

    VAR __LAST_DATE = ENDOFMONTH('Table1'[Date].[Date])

    VAR __DATE_PERIOD =

        DATESBETWEEN(

            'Table1'[Date].[Date],

            STARTOFMONTH(DATEADD(__LAST_DATE, -12, MONTH)),

            __LAST_DATE

        )

    VAR Result =

        AVERAGEX(

            CALCULATETABLE(

                SUMMARIZE(

                    VALUES('Utilization'),

                    'Table1'[Date].[Year],

                    'Table1'[Date].[QuarterNo],

                    'Table1'[Date].[Quarter],

                    'Table1'[Date].[MonthNo],

                    'Utilization'[Date].[Month]

                ),

                __DATE_PERIOD

            ),

            CALCULATE(SUM('Table1'[Hours]), ALL('Table1'[Date].[Day]))

        )

VAR FirstDateInPeriod = MINX ( __DATE_PERIOD, 'Table1'[Date].[Date] )

VAR LastDateWithHrs = MAX ( 'Table1'[Date (bins)])

RETURN

IF ( FirstDateInPeriod <= LastDateWithHrs, Result )

)

[Ashish_Mathur]

Hi,

Share the link from where i can download your PBI file.  Also, when you say rolling average for August 2021, is it the rolling average for the 12 months ended August 2021? 

[lbendlin]

Table2 has many to one to the Table1). Based on that key, I am trying to bring the column "hours" and "rolling average"  to the second table".

If you created a measure then it will work regardless of the table. If you created a calculated column then you can use the RELATED() function if you need to "bring the column over". The question would be - what's the need?  Your visuals will work without that too.