cancel
Showing results for
Did you mean:
Frequent Visitor

## Trend of a moving average

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?

1 ACCEPTED SOLUTION
Resolver III

Hi @renmello ,

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]

Hope this helps.

Best regards

Mikelytics

2 REPLIES 2
Frequent Visitor

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

Resolver III

Hi @renmello ,

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]

Hope this helps.

Best regards

Mikelytics

Announcements