cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Helper III
Helper III

Rolling 12 Month Sales Average but correctly averaging the first 11 months of Sales

Hi All,

 

I've calculated a rolling 12 month average but want the first 11 months of sales to average correctly.

Example, The first month of sales rolling average should only average the first month sales and not go back to months of 0 sales.

The send month of sales should only average the first 2 months with no 0s, etc up until 12 full months of sales.

 

Below is the line graph and my current rolling 12 month calculation. 

SalesAvgValue is just the average of all sales.

 

Can anyone help?

 

Capture.PNGCapture.PNG

1 ACCEPTED SOLUTION

Hi,

 

You may refer to my solution in this PBI file.

 

Hope this helps.

 

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

6 REPLIES 6
Helper III
Helper III

Below is a dataset with sales and then the 12 month average sales.

As you can see the first 11 months where the product first generates sales only averages back to when the sales started not a full 12 months back each month.  Once 12 months of sales have occured then the average goes back 12 months the rest of the way.

I just need to know how to account for an average of those first 11 months by product line (only included 1 below) so it isn't accounting for months with 0 revenue prior to each product launch.

 

Let me know if this makes sense.

 

 

ProductNamePeriodSales12 MO Average of Sales
Alpha1/1/200500
Alpha2/1/200500
Alpha3/1/200500
Alpha4/1/200500
Alpha5/1/200500
Alpha6/1/200500
Alpha7/1/200500
Alpha8/1/200500
Alpha9/1/200500
Alpha10/1/200500
Alpha11/1/200500
Alpha12/1/200500
Alpha1/1/200600
Alpha2/1/200600
Alpha3/1/200600
Alpha4/1/200600
Alpha5/1/200600
Alpha6/1/200600
Alpha7/1/200600
Alpha8/1/200600
Alpha9/1/200600
Alpha10/1/20062743627436
Alpha11/1/20069450160969
Alpha12/1/20068382568587
Alpha1/1/200712704783202
Alpha2/1/200712800892163
Alpha3/1/2007140058100146
Alpha4/1/2007133356104890
Alpha5/1/2007168260112811
Alpha6/1/2007176925119935
Alpha7/1/2007134610121403
Alpha8/1/2007167552125598
Alpha9/1/2007200056131803
Alpha10/1/2007111366138797
Alpha11/1/2007147166143186
Alpha12/1/2007115349145813
Alpha1/1/2008172985149641
Alpha2/1/2008222333157501
Alpha3/1/2008156303158855
Alpha4/1/2008160621161127
Alpha5/1/2008177965161936
Alpha6/1/2008178935162103
Alpha7/1/2008143709162862
Alpha8/1/2008244898169307
Alpha9/1/2008180998167719

 

Hi,

 

You may refer to my solution in this PBI file.

 

Hope this helps.

 

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

Worked, thanks so much!

You are welcome.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Community Support
Community Support

Hi chudson,

 

You said "Example, The first month of sales rolling average should only average the first month sales and not go back to months of 0 sales.The send month of sales should only average the first 2 months with no 0s, etc up until 12 full months of sales."

<-- What does this mean, could you please provide a demo or clarify more details about your expected requirement?

 

Regards,

Jimmy Tao

Super User III
Super User III

Hi,

 

Share a dataset and show the expected result in a Table form.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Helpful resources

Announcements
secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

Microsoft Ignite

Microsoft Ignite

Join digitally, March 2–4, 2021 to explore new tech that's ready to implement. Experience the keynote in mixed reality through AltspaceVR!

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.

Top Solution Authors
Top Kudoed Authors