Rolling Average in DAX | 2 different tables

[Raitup00]

Hi everyone

I hope you can help me with this. I have 2 tables, each one with a Date column (dd-mmm-yyyy) and Quantity, both tables are related with my CalendarTable. I have a filter to select the month and year always from Table1 and I want that after selecting month-year in the filter, the system calculate the average of the quantity of 6 months: 3 months (previous) from Table1 and 2 months (after) of the Table2 in order to get 6 months in total.

I've used @DATESINPERIOD function but I don't know how to reference for both table. 

Graphycally I want this: 

Thanks in advance,

Raitup00

[amitchandak]

@Raitup00 , Based on what I got.

Rolling 6 = CALCULATE(sum(Table2[Value]),DATESINPERIOD('Date'[Date ],eomonth(MAX('Date'[Date ]),1),-3,MONTH)) + CALCULATE(sum(Table1[Value]),DATESINPERIOD('Date'[Date ],eomonth(MAX('Date'[Date ]),-2),-3,MONTH)) 

[Raitup00]

Thanks @amitchandak for your time

Unfortunatly the option you gave is not what I'm dealing with. Actuually I don´t need a "Rolling" average. I need that DAX calculates the average per month and the month selected must be as a switch between Tables. For example, if I select May-2022, DAX should be able to calcule the Average for

Feb-2022 (-3 month - Table1),

Mar-2022 (-2 month - Table1),

Apr-2022 (-1 month - Table1),

May-2022 (selected month - Table1),

Jun-2023 (+1 month - Table2),

Jul-2023 (+2 month - Table 2)

I hope you can help me again.

Thank in advance

[v-zhangti · Accepted solution]

Hi, @Raitup00 

You can try the following methods.

Table:

Calendar = CALENDAR(DATE(2022,1,1),DATE(2022,12,31))

Year = YEAR([Date])

Month = MONTH([Date])

The relationship between the three tables is shown in the figure, and the filter also uses the calendar table.

Column:

Year month = YEAR([T1_Date])&"-"&MONTH([T1_Date])

Average 1 = 
CALCULATE(AVERAGE('Table 1'[T1_Qty]),FILTER('Table 1',[Year month]=EARLIER('Table 1'[Year month])))

Average 2 = 
CALCULATE(AVERAGE('Table 2'[T2_Qty]),FILTER('Table 2',[Year month]=EARLIER('Table 2'[Year month])))

Measure:

3 months (previous) =
CALCULATE (
    SUMX (
        SUMMARIZE ( 'Table 1', 'Table 1'[Average 1], 'Table 1'[Year month] ),
        [Average 1]
    ),
    FILTER (
        ALL ( 'Table 1' ),
        [T1_Date]
            >= DATE ( SELECTEDVALUE ( 'Calendar'[Year] ), SELECTEDVALUE ( 'Calendar'[Month] ) - 3, 1 )
            && [T1_Date]
                <= DATE ( SELECTEDVALUE ( 'Calendar'[Year] ), SELECTEDVALUE ( 'Calendar'[Month] ) + 1, 1 ) - 1
    )
)

2 months (after) =
CALCULATE (
    SUMX (
        SUMMARIZE ( 'Table 2', 'Table 2'[Average 2], 'Table 2'[Year month] ),
        [Average 2]
    ),
    FILTER (
        ALL ( 'Table 2' ),
        [T2_Date]
            >= DATE ( SELECTEDVALUE ( 'Calendar'[Year] ), SELECTEDVALUE ( 'Calendar'[Month] ) + 1, 1 )
            && [T2_Date]
                <= DATE ( SELECTEDVALUE ( 'Calendar'[Year] ), SELECTEDVALUE ( 'Calendar'[Month] ) + 3, 1 ) - 1
    )
)

Result = DIVIDE([3 months (previous)]+[2 months (after)],6)

Please refer to the attachment for detailed steps.

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Raitup00]

Thank you so mucho @v-zhangti