topic Rolling average based on month in DAX Commands and Tips

Rolling average based on month

Kev59 — Wed, 03 Apr 2024 12:50:51 GMT

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

Re: Rolling average based on month

lbendlin — Wed, 03 Apr 2024 22:12:26 GMT

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

Re: Rolling average based on month

Kev59 — Thu, 04 Apr 2024 07:15:48 GMT

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