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.
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]) ) ) )
Solved! Go to Solution.
You can write something like
=IF( [OtherMeasureInChart]<>Blank(), [Rolling Avg Measure] )
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...
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
You can write something like
=IF( [OtherMeasureInChart]<>Blank(), [Rolling Avg Measure] )
Your simple trick saved my day..
Thanks mate
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
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
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.
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 ?
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
97 | |
84 | |
67 | |
60 |
User | Count |
---|---|
150 | |
120 | |
99 | |
87 | |
68 |