cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
renmello
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.Media Movel 1.JPG
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.

Media Movel 2.JPG

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
Mikelytics
Resolver III
Resolver III

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.
 
Picture 1.PNG

 

Hope this helps.

 

Best regards

Mikelytics

 

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

 

Appreciate your Kudos.

View solution in original post

2 REPLIES 2
renmello
Frequent Visitor

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
Resolver III
Resolver III

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.
 
Picture 1.PNG

 

Hope this helps.

 

Best regards

Mikelytics

 

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

 

Appreciate your Kudos.

View solution in original post

Helpful resources

Announcements
User Groups Public Preview

Join us for our User Group Public Preview!

Power BI User Groups are coming! Make sure you’re among the first to know when user groups go live for public preview.

March Update

Check it Out!

Click here to read more about the March 2021 Updates!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.