Quick measure - Moving average

[MartinHA]

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])
			)
		)
)

[Greg_Deckler]

See if my Time Intelligence the Hard Way provides a different way of accomplishing what you are going for.

https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TIT...

[MartinHA]

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 · Accepted solution]

You can write something like

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

[Anonymous]

Your simple trick saved my day..

Thanks mate

[MartinHA]

Thank you for your answer, that's work perfectly.

I have a last question, is it possible to remove the first 2 month of 2016 on the chart for the measure because this is not calculated on a 3 month basis due to filter applied on the report.

Thank you

Martin

[ChandeepChhabra]

Hi @MartinHA

You can further wrap the entire IF function in the CALCULATE Function. Something like

=CALCULATE (
[EntireIFfunctionMeasure],
DATESBETWEEN ( Cal[Date], DATE ( 2016, 3, 1 ), MAX ( Cal[Date] ) ) )

Cal[Date] - will come from your calendar table. Should be related to your transactions table

DATESBETWEEN Function - will allow you to run the calculation between set time periods. In this case the start date has been manually punched as 1st Mar 2016 by using the DATE function

I hope that works

[MartinHA]

Hi,

Thanks for your answer. It's not working as the rolling average for march is equal to march data. The calculation seems to begin in march 2016.

[ChandeepChhabra]

@MartinHA

I am not sure if I have understood your question correctly.

When you say remove the first 2 months - You mean still want the rolling total to start from Jan but don't want to show Jan and Feb in horizontal axis of the chart ?

Can you please elaborate ?

[MartinHA]

@ChandeepChhabra

Well what i want is to show a good calculation of the rolling average. With my formula the calculation only begin in march, so the rolling haverag is equal to march value. For April the calulation is (march + april)/2 wich is still incorect.

To have what i want i need to begin my measure at this date (2015;10;1) to have the rolling average for january. So is it possible to hide 2015?

Thank you for your precious help

Martin