Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Quick measure - Moving average

Hello,

 

I'm new with power BI and i have trouble calculating moving average.

 

I use the quick measure option to calculate 3 month moving average and i get this result:

 

 

 

 

It seems that the results are not good. For exemple in december i should have 3403 instead of 3280,5. Can you please helps me to find what is wrong with my formula?

 

 

The formula used is the one below

Moyenne mobile de Nombre de ininter = 
IF(
	ISFILTERED('v_ext_details_reporting_mensuel'[dadate]);
	ERROR("Les mesures rapides de Time Intelligence peuvent être regroupées ou filtrées seulement par la hiérarchie de dates ou les colonnes de dates principales fournies par Power BI.");
	VAR __LAST_DATE = ENDOFMONTH('v_ext_details_reporting_mensuel'[dadate].[Date])
	VAR __DATE_PERIOD =
		DATESBETWEEN(
			'v_ext_details_reporting_mensuel'[dadate].[Date];
			STARTOFMONTH(DATEADD(__LAST_DATE; -3; MONTH));
			__LAST_DATE
		)
	RETURN
		AVERAGEX(
			CALCULATETABLE(
				SUMMARIZE(
					VALUES('v_ext_details_reporting_mensuel');
					'v_ext_details_reporting_mensuel'[dadate].[Année];
					'v_ext_details_reporting_mensuel'[dadate].[NoTrimestre];
					'v_ext_details_reporting_mensuel'[dadate].[Trimestre];
					'v_ext_details_reporting_mensuel'[dadate].[NoMois];
					'v_ext_details_reporting_mensuel'[dadate].[Mois]
				);
				__DATE_PERIOD
			);
			CALCULATE(
				DISTINCTCOUNT('v_ext_details_reporting_mensuel'[ininter]);
				ALL('v_ext_details_reporting_mensuel'[dadate].[Jour])
			)
		)
)
  • You can write something like

    =IF( [OtherMeasureInChart]<>Blank(), [Rolling Avg Measure] )

9 Replies

    • Anonymous's avatar
      Anonymous
      Not applicable

      Thanks for your answer,

       

      I've found a solution to my problem, for a 3 month rolling average i have to write "-2" instead of "-3".

       

      I have now another question.

       

      You can see in my chart that rolling average is calculated for future month (January, February and March 2019). How can i stop the measure so that i don't have a wrong presentation?

       

      Thanks

      Martin

      • ChandeepChhabra's avatar
        ChandeepChhabra
        Impactful Individual

        You can write something like

        =IF( [OtherMeasureInChart]<>Blank(), [Rolling Avg Measure] )