Averagex over multiple dates

[haputhanthree]

Hi,

I have an unrelated calendar table and the below measures in the sales table. 

InTransit =

 CALCULATE(

    COUNT(Sales[ID])

    ,FILTER(

        'Sales'

        ,Sales[DeliveryDate] < MAX('Dim Calendar'[Date])

           && Sales[ArrivalDate] > MAX('Dim Calendar'[Date])

    )

)

InTransit - The number of orders that have been delivered by the end of the month but have not been delivered yet. 

I want to calculate the average for the last 2 months as (# of last month's in transit + this moth transit)/2 

But the below measure is not working as expected. Look forward to your support. 

Average Lst 2 Months =

AVERAGEX(

    VALUES('Dim Calendar'[Month])

    ,[InTransit]

)
Current result 

Expected Result
 
Link to PBIX file 

[haputhanthree · Accepted solution]

The below measue worked for me. 

 Average Lst 2 Months =
VAR NumOfMonths = 2
VAR LastCurrentDate =
    MAX ( 'Dim Calendar'[Date] )
VAR Period =
    DATESINPERIOD ( 'Dim Calendar'[Date], LastCurrentDate, - NumOfMonths, MONTH )
VAR result =
    AVERAGEX (
        SUMMARIZE (
            CALCULATETABLE ( 'Dim Calendar', Period ),
            'Dim Calendar'[Month],
            "InTransit", [InTransit]
        ),
        [InTransit]
    )
RETURN
    result

[haputhanthree]

@Jihwan_Kim  Thank you!

If I want to calculate average of last 12 moths must define 12 variables. Is there any optimization that you could think off to handle that scenario?

[Jihwan_Kim]

Hi,

Please check the attached pbix file.