Rolling average same period last year

[James__]

Hi,

I have a rolling average measure which works as it should: 

@rolling_avg_sales_per_day = AVERAGEX(FILTER(ALLSELECTED('Calendar'),'Calendar'[Date]<=MAX('Calendar'[Date])),[@@@product_sales])

I then try and look for the same rolling average for the same period last year, but not getting the desired result. I have tried 2 measures without success:

1. 

@rolling_avg_sales_per_day_SPLY = CALCULATE([@rolling_avg_sales_per_day],SAMEPERIODLASTYEAR('Calendar'[Date]))

2. 

@rolling_avg_sales_per_day_SPLY = CALCULATE([@rolling_avg_sales_per_day],DATEADD('Calendar'[Date],-1,YEAR))

instead i am getting this:

Can anyone help with the correct measure, please?

Thanks

James 

[v-tangjie-msft · Accepted solution]

Hi @James__ ,

According to your description, here are my steps you can follow as a solution.

You can create a measure as below:

Sales R12M = 
VAR NumOfMonths = 12
VAR LastCurrentDate =
    MAX ( 'Date'[Date] )
VAR Period =
    DATESINPERIOD ( 'Date'[Date], LastCurrentDate, - NumOfMonths, MONTH )
VAR Result =
    CALCULATE (
        AVERAGEX (
            VALUES ( 'Date'[YearMonth]),
            [Sales Amount]
        ),
        Period
    )
VAR FirstDateInPeriod = MINX ( Period, 'Date'[Date] )
VAR LastDateWithSales = MAX ( Sales[Order Date] )
RETURN
    IF ( FirstDateInPeriod <= LastDateWithSales, Result )

Then the result is as follows.

You can refer to the following documents that may be helpful to you:

Rolling 12 Months Average in DAX - SQLBI

Solved: Rolling 12M Average only calculate average on year... - Microsoft Power BI Community

Cumulative rolling average by day/month - Microsoft Power BI Community

Solved: Need help with rolling periods past 12M, 6M, P3M a... - Microsoft Power BI Community

Best Regards,

Neeko Tang

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