Rolling average based on month

[Kev59]

Dear all

I use the following code to calculate the revenues generated for the last 12 months (excluding the current one).

Last 12 Months = 

VAR FiscalTodayIndex =
    CALCULATE (
        MAX ( 'Calendar Fiscal'[Index] ),
        FILTER ( 'Calendar Fiscal', 'Calendar Fiscal'[TFS Date] = TODAY() )
    )
VAR StartIndex = FiscalTodayIndex - 12
VAR EndIndex = FiscalTodayIndex - 1

Var Result =
CALCULATE(
	switch(
        TRUE(),
        SELECTEDVALUE(_Metric[_Metric Commande])=0,SUM('CDD - Invoiced Sales'[Quantity in Base UOM]),
    	SELECTEDVALUE(_Metric[_Metric Commande])=1,SUM('CDD - Invoiced Sales'[Net Sales USD])	    
	),
	FILTER(ALL('Calendar Fiscal'),
    'Calendar Fiscal'[Index] >= StartIndex &&
    'Calendar Fiscal'[Index] <= EndIndex )
)
return Result

I would like to calculate the Monthly rolling average based on the same principle (excluding the current one).

How to do that propertly? 

Currently I use the following code but the calculation is not good...

RollingAverage = 

VAR NumOfMonths = '_Rolling Average Period'[Valeur _Rolling Average Period] 
VAR LastCurrentDate = LASTDATE( 'Calendar Fiscal'[TFS Date] )
VAR TimePeriod = DATESINPERIOD ( 'Calendar Fiscal'[TFS Date], LastCurrentDate, - NumOfMonths, MONTH )

VAR Result = 
CALCULATE(
	AVERAGEX(
		SUMMARIZE(
			'Calendar Fiscal',
			'Calendar Fiscal'[TFS Year],
			'Calendar Fiscal'[TFS Month Number ]),
 		switch(
        TRUE(),
        SELECTEDVALUE(_Metric[_Metric Commande])=0,[Period_Qty],
    	SELECTEDVALUE(_Metric[_Metric Commande])=1,[Period_Revenues])	    
	),
	
	TimePeriod
)
VAR LastDateWithSale = MAX ('CDD - Invoiced Sales'[Posting Date])
VAR FirstVisibleDate = MINX(TimePeriod,'Calendar Fiscal'[TFS Date])

RETURN

IF (FirstVisibleDate <= LastDateWithSale , Result)

Thanks for your help

BR

[Kev59 · Accepted solution]

Hi @lbendlin 

Sorry my English is quite poor.

I find a solution with this code

RollingAverage2 = 

VAR NumOfMonths = '_Rolling Average Period'[Valeur _Rolling Average Period] 
VAR FiscalTodayIndex =  MAX ( 'Calendar Fiscal'[Index] )
VAR StartIndex = FiscalTodayIndex - NumOfMonths +1
VAR EndIndex = FiscalTodayIndex

Var Result =
CALCULATE(
	switch(
        TRUE(),
        SELECTEDVALUE(_Metric[_Metric Commande])=0,DIVIDE(SUM('CDD - Invoiced Sales'[Quantity in Base UOM]),NumOfMonths),
    	SELECTEDVALUE(_Metric[_Metric Commande])=1,DIVIDE(SUM('CDD - Invoiced Sales'[Net Sales USD]),NumOfMonths)	    
	),
	FILTER(ALL('Calendar Fiscal'),
    'Calendar Fiscal'[Index] >= StartIndex &&
    'Calendar Fiscal'[Index] <= EndIndex )
)
return Result

And the calculation results are good

BR

[lbendlin]

What is your rolling average window?  Speaking of which - try the WINDOW function.