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 ( MA...
  • 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

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