topic Re: Moving average / moving variance in DAX Commands and Tips
https://community.powerbi.com/t5/DAX-Commands-and-Tips/Moving-average-moving-variance/m-p/1034225#M13691
<P>Hi Greg,<BR />Thanks for your answer, but I guess this is not the problem in this case, though it may seem so.<BR /><BR />If I just choose the year as my dimension, the value still remains the same, although there is no 'Total' lines involved.</P><P><span class="lia-inline-image-display-wrapper lia-image-align-inline" image-alt="Movingaverage_year.PNG" style="width: 316px;"><img src="https://community.powerbi.com/t5/image/serverpage/image-id/257838i8D92380C55E75741/image-size/large?v=1.0&px=999" title="Movingaverage_year.PNG" alt="Movingaverage_year.PNG" /></span></P><DIV class="mceNonEditable lia-copypaste-placeholder"> </DIV>Sat, 18 Apr 2020 20:54:46 GMTIMett2020-04-18T20:54:46ZMoving average / moving variance
https://community.powerbi.com/t5/DAX-Commands-and-Tips/Moving-average-moving-variance/m-p/1033291#M13654
<P>Hi,<BR />I want to calculate a moving average of my measure over the last 12 months. </P><P>I use the following DAX formula so far, which works, as long as my time granularity level is the month. </P><P> </P><LI-CODE lang="python">AVG_Produktion =
CALCULATE (
[PRODUKTION];
DATESINPERIOD (
Datumstabelle[Date];
LASTDATE ( Datumstabelle[Date] );
-12;
MONTH
)
) / 12
* DIVIDE ( [PRODUKTION]; [PRODUKTION] )</LI-CODE><P> </P><P> </P><P>The results look like this:</P><P>The value for Jan 2022 is correct (average of the months Feb 2021 - Jan 2022), however the value for the whole year 2022 is obviously not correct - I'm actually not completely sure, where this value comes from.<BR />What I want to see here is the sum of all the MA values for 2022, so that I can compare it to the actual yearly production.<BR />Similarly, if I go down on the date level, I want to know, what the average daily production was in the last 12 months.</P><P> </P><DIV class="mceNonEditable lia-copypaste-placeholder"> </DIV><P> <span class="lia-inline-image-display-wrapper lia-image-align-inline" image-alt="Movingaverage.PNG" style="width: 402px;"><img src="https://community.powerbi.com/t5/image/serverpage/image-id/257639iD4FA785106834319/image-size/large?v=1.0&px=999" title="Movingaverage.PNG" alt="Movingaverage.PNG" /></span></P><P> </P><P>I assume, there should be some kind of an elegant solution using the average or averagex function.</P><P>The other question is how to compute a moving variance for the same time periods, ideally without having to calculate the variance formula by hand, bur rather using the VARX.P function.</P>Fri, 17 Apr 2020 19:57:03 GMThttps://community.powerbi.com/t5/DAX-Commands-and-Tips/Moving-average-moving-variance/m-p/1033291#M13654IMett2020-04-17T19:57:03ZRe: Moving average / moving variance
https://community.powerbi.com/t5/DAX-Commands-and-Tips/Moving-average-moving-variance/m-p/1033328#M13655
<P>This looks like a measure totals problem. Very common. See my post about it here: <A href="https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376" target="_blank">https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376</A><BR /><BR />Also, this Quick Measure, Measure Totals, The Final Word should get you what you need:<BR /><A href="https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907" target="_blank">https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907</A></P>Fri, 17 Apr 2020 20:24:13 GMThttps://community.powerbi.com/t5/DAX-Commands-and-Tips/Moving-average-moving-variance/m-p/1033328#M13655Greg_Deckler2020-04-17T20:24:13ZRe: Moving average / moving variance
https://community.powerbi.com/t5/DAX-Commands-and-Tips/Moving-average-moving-variance/m-p/1034225#M13691
<P>Hi Greg,<BR />Thanks for your answer, but I guess this is not the problem in this case, though it may seem so.<BR /><BR />If I just choose the year as my dimension, the value still remains the same, although there is no 'Total' lines involved.</P><P><span class="lia-inline-image-display-wrapper lia-image-align-inline" image-alt="Movingaverage_year.PNG" style="width: 316px;"><img src="https://community.powerbi.com/t5/image/serverpage/image-id/257838i8D92380C55E75741/image-size/large?v=1.0&px=999" title="Movingaverage_year.PNG" alt="Movingaverage_year.PNG" /></span></P><DIV class="mceNonEditable lia-copypaste-placeholder"> </DIV>Sat, 18 Apr 2020 20:54:46 GMThttps://community.powerbi.com/t5/DAX-Commands-and-Tips/Moving-average-moving-variance/m-p/1034225#M13691IMett2020-04-18T20:54:46ZRe: Moving average / moving variance
https://community.powerbi.com/t5/DAX-Commands-and-Tips/Moving-average-moving-variance/m-p/1034305#M13695
<P>402.382 / 12 = 33.532.<BR /><BR />The calculation is correct.<BR /><BR />The Gesamt is correct as well according to the formula and to what one would expect. That's because it's not just an average but a 12-month monthly moving average.<BR /><BR />Best<BR />D</P>Sat, 18 Apr 2020 23:33:51 GMThttps://community.powerbi.com/t5/DAX-Commands-and-Tips/Moving-average-moving-variance/m-p/1034305#M13695Anonymous2020-04-18T23:33:51ZRe: Moving average / moving variance
https://community.powerbi.com/t5/DAX-Commands-and-Tips/Moving-average-moving-variance/m-p/1039774#M13906
<P>Hi,<BR />maybe the term "moving average" is misleading or even wrong here. I'll try to illustrate what I want to have with a simple Excel example:</P><P>Imagine I have raw data per month. The 12M-moving average for January 2020 (=C14) is AVERAGE(B2:B13). [or maybe Average(B3:B14, if you take the new value into the timeframe, there are varying definitions I guess]<BR />With this formula, I can calculate the moving average for every month after the 12th.</P><P><span class="lia-inline-image-display-wrapper lia-image-align-inline" image-alt="movingaverage_raw_excel.PNG" style="width: 357px;"><img src="https://community.powerbi.com/t5/image/serverpage/image-id/258979i65B999FC2B71B14B/image-size/medium?v=1.0&px=400" title="movingaverage_raw_excel.PNG" alt="movingaverage_raw_excel.PNG" /></span></P><DIV class="mceNonEditable lia-copypaste-placeholder"> </DIV><P>Now as long as I <STRONG>display </STRONG>this measure on the month level, everything is fine, I can simply display the calculated values without any further aggregation.<BR />But if I choose to aggregate on the year level (or quarter, whatever), then I have to take the SUM of all the values in column C, not their average!<BR />If I create a Pivot Table in Excel, this is exactly what happens. But I don't know how to achieve the same behaviour in PowerBI.</P><P> </P><P><span class="lia-inline-image-display-wrapper lia-image-align-inline" image-alt="movingaverage_pivot_excel.PNG" style="width: 400px;"><img src="https://community.powerbi.com/t5/image/serverpage/image-id/258978i5DECE493FECB2709/image-size/medium?v=1.0&px=400" title="movingaverage_pivot_excel.PNG" alt="movingaverage_pivot_excel.PNG" /></span></P><DIV class="mceNonEditable lia-copypaste-placeholder"> </DIV>Tue, 21 Apr 2020 16:12:08 GMThttps://community.powerbi.com/t5/DAX-Commands-and-Tips/Moving-average-moving-variance/m-p/1039774#M13906IMett2020-04-21T16:12:08ZRe: Moving average / moving variance
https://community.powerbi.com/t5/DAX-Commands-and-Tips/Moving-average-moving-variance/m-p/1040123#M13918
<P>You are mixing 2 different measures. One is the 12-month average and the other is the sum of the 12-month averages. If you want to have these 2 morphed into one... then you'll need to write a SWITCH which will select the measure based on what time period is in scope.</P>
<P> </P>
<P>Actually... on reflection, you can do something simpler: you can write a measure that will sum up the averages over the currently visible pieces of time. If you never go below the month level, then it's even easier. Hide the individual dates so that they can't be selected, leave only pieces of time from the month up and write:</P>
<P> </P>
<P>sumx(<BR /> VALUES ( Calendar[YearMonth] ),</P>
<P> [12M Average]</P>
<P>)</P>
<P> </P>
<P>This will give you what you want.</P>
<P><BR />Best<BR />D</P>Tue, 21 Apr 2020 19:33:51 GMThttps://community.powerbi.com/t5/DAX-Commands-and-Tips/Moving-average-moving-variance/m-p/1040123#M13918Anonymous2020-04-21T19:33:51ZRe: Moving average / moving variance
https://community.powerbi.com/t5/DAX-Commands-and-Tips/Moving-average-moving-variance/m-p/1043479#M14029
<P>That looks good, thank you!<BR /><BR />Although it didn't solve my intiial question on how to calculate the moving variance, accordingly, without having to write the variance formula explicitly. I hoped to find a way to calculate the desired moving average by using the function Averagex and adapt the filter settings to a different measure with the VARX.P function.</P>Thu, 23 Apr 2020 07:38:46 GMThttps://community.powerbi.com/t5/DAX-Commands-and-Tips/Moving-average-moving-variance/m-p/1043479#M14029IMett2020-04-23T07:38:46Z