Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
Kev59
Frequent Visitor

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

1 ACCEPTED SOLUTION
Kev59
Frequent Visitor

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

View solution in original post

2 REPLIES 2
Kev59
Frequent Visitor

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
Super User
Super User

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

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors