Forum Discussion

Kev59's avatar
Kev59
Icon for Helper I rankHelper I
2 years ago
Solved

Rolling average based on month

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's avatar
    Kev59
    2 years ago

    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

2 Replies

  • lbendlin's avatar
    lbendlin
    Icon for Super User rankSuper User
    2 years ago

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

  • Kev59's avatar
    Kev59
    Icon for Helper I rankHelper I
    2 years ago

    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