Trend of a moving average

[renmello]

Hello, everyone. 

I was practing my skills with covid data from Brazil and i am stuck calculating the trend of the moving average of 10 days from new cases.

I have this table with the date, the new cases each day and the moving average of 10 days for the day.
What i would like to do is to select the moving average from lastdate and divide by only the moving average of 10 days ago.



I am able to select only the moving average of lastdate, but when i try to only select the moving average of 10 days ago it returns the last moving average. 

Anyone have any ideas? 

[renmello]

Hello, thanks for the reply!

It worked perfectly. Dateadd is what i was missing to select the moving average from 14 days ago. Thanks for the help 🙂 

[Mikelytics · Accepted solution]

Hi @renmello ,

Please try the following.

I did it with a 3 days average and build 3 measures (average, average from 3 days ago and comparison)

3 day average = CALCULATE(AVERAGE(fact_table_d[Sales]),DATESBETWEEN('Date'[Date],MAX('Date'[Date])-2,MAX('Date'[Date])))

get measure value from 3 days ago = CALCULATE([3 day average],DATEADD('Date'[Date],-3,DAY))

Compare both measures = [3 day average] / [get measure value from 3 days ago]

Please find below the results.

Hope this helps.

Best regards

Mikelytics

Did I solve your request? Please mark my post as solution.

Appreciate your Kudos.