topic Re: Problem with Moving Average 12 in DAX Commands and Tips

Problem with Moving Average 12

Anonymous — Sat, 29 Aug 2020 00:37:19 GMT

Hi.

I have an issue. I was trying to replicate the formula from this post: https://community.powerbi.com/t5/Desktop/5-Day-Moving-average-No-Weekends-in-Data-and-how-I-skip-weekends/m-p/133197#M56901 in a Moving average of 12 data.

I have stocks information, then I just have information on weekdays, replicating this formula worked in moving average of 5, but I need to use it on 12 as well. Following is the formula:

Movil 12 = AVERAGEX(DATESINPERIOD(Tecnicos[FECHA],MAX(Tecnicos[FECHA]),-16,DAY),[Total Precios])

The problem is that on mondays I get the wrong average, because counting 16 gets to a sunday (blank) and I need to get to the previous friday. But If I try to move the -16 to a -17 all the other averages will be wrong.

I tried looking for that information in many places I couldn't make it.

I aprecciate help.

Re: Problem with Moving Average 12

CNENFRNL — Sat, 29 Aug 2020 02:16:27 GMT

Hi, there

Weekends are supposed to be filtered out in Tecnicos[FECHA] column beforehand;

or filter down Tecnicos[FECHA] if it contains all consecutive dates

_12workdays = TOPN ( 12, CALCULATETABLE ( VALUES ( Calendar[Date] ), NOT WEEKDAY ( Calendar[Date] ) IN { 1, 7 }, FILTER ( ALL ( Calendar[Date] ), Calendar[Date] <= MAX ( Calendar[Date] ) ) ) )

Re: Problem with Moving Average 12

Greg_Deckler — Sat, 29 Aug 2020 02:16:53 GMT

Anonymous - One, I would personally avoid DATESINPERIOD. https://community.powerbi.com/t5/Quick-Measures-Gallery/To-bleep-With-DATEADD/m-p/1259467#M583

You may find this helpful - https://community.powerbi.com/t5/Community-Blog/To-bleep-With-Time-Intelligence/ba-p/1260000

Also, 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-TITHW/m-p/434008

Finally, Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.

Re: Problem with Moving Average 12

Anonymous — Sat, 29 Aug 2020 15:11:15 GMT

Hi.

Thank you for your feedback.

I tried using the formulas, however I couldn't make it.

I'm using a lot of companies to analyze, my original chart has dates in rows and companies in columns, therefore I had to unpivot table to use it in BI properly, That's how my table looks like (unpivot)

FECHA	Empresa	Price
23/12/2009	Acerias	$39.40
23/12/2009	Isagen	$2,165.00
23/12/2009	GrupoArgos	$18,980.00
24/12/2009	Acerias	$41.65
24/12/2009	Isagen	$2,200.00
24/12/2009	GrupoArgos	$19,000.00

That's an example. I have more than 2000 rows with more companies (Data since 2009)

What I was using with the dateadd function was MovingAverage12 = AVERAGEX(DATESINPERIOD(Tecnicos[FECHA],MAX(Tecnicos[FECHA]),-16,DAY),[Total Precios])

The table I am receiving is the next one:

FECHA	Price	MovingAverage12
Friday, August 14, 2020	10,260.00	10,313.00
Monday, August 17, 2020	10,260.00	10,315.00
Tuesday, August 18, 2020	10,520.00	10,332.00
Wednesday, August 19, 2020	10,660.00	10,357.00
Thursday, August 20, 2020	10,740.00	10,402.00
Friday, August 21, 2020	10,980.00	10,453.00
Monday, August 24, 2020	11,320.00	10,549.00
Tuesday, August 25, 2020	11,860.00	10,658.00
Wednesday, August 26, 2020	11,820.00	10,785.00
Thursday, August 27, 2020	11,900.00	10,920.00

The values I'm looking for, are the following.

Friday, August 14, 2020	10,260.00	10,313.33
Monday, August 17, 2020	10,260.00	10,305.00
Tuesday, August 18, 2020	10,520.00	10,331.67
Wednesday, August 19, 2020	10,660.00	10,356.67
Thursday, August 20, 2020	10,740.00	10,401.67
Friday, August 21, 2020	10,980.00	10,453.33
Monday, August 24, 2020	11,320.00	10,533.33
Tuesday, August 25, 2020	11,860.00	10,658.33
Wednesday, August 26, 2020	11,820.00	10,785.00
Thursday, August 27, 2020	11,900.00	10,920.00

As you may see, monday's averages are wrong because takes data from previous 16 days. In addition, Dates Column (Fecha) Just have dates from monday to friday, there are no weekends on that table as I'm not using it.

Thank you once again for your support. I'll appreciate your help once again.

Re: Problem with Moving Average 12

Anonymous — Sat, 29 Aug 2020 15:14:25 GMT

Hi.

Thank you for your answer.

The column FECHA that contains dates just have dates from monday to friday, however in the calculation is taking weekends as well (I Think because I'm using DATESINPERIOD).

Re: Problem with Moving Average 12

Greg_Deckler — Sat, 29 Aug 2020 15:47:53 GMT

Anonymous - Can you just use this?

https://community.powerbi.com/t5/Quick-Measures-Gallery/Rolling-Average/m-p/160720#M3

Re: Problem with Moving Average 12

daxer-almighty — Sun, 30 Aug 2020 17:49:04 GMT

// For this stuff to work you need // Calendar (a date table in the model). [12D MA] = var __maxVisibleDay = MAX( 'Calendar'[Date] ) var __countOfDaysToAvgOver = 12 var __datesToAvgOver = TOPN( __countOfDaysToAvgOver, // This table gets you the correct // number of days of the correct // type but you have to remember // that you might be too close // to the beginning of the // Calendar and in fact you'll // get fewer days than needed. CALCULATETABLE( VALUES( 'Calendar'[Date] ), 'Calendar'[DayType] = "Weekday", 'Calendar'[Date] <= __maxVisibleDay, ALL( 'Calendar' ) ), 'Calendar'[Date], DESC ) // This check is needed in case there // are not enough days in the calendar // (see the comment above). var __thereAreEnoughDays = COUNTROWS( __datesToAvgOver ) = __countOfDaysToAvgOver return if( __thereAreEnoughDays, CALCULATE( AVERAGEX( __datesToAvgOver, [Total Precios] ), // This directive is probably not // needed but only if 'Calendar' // is a proper Date table in // the model. But you can leave it // in as it works in any setting. ALL( 'Calendar' ) ) )