cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Helper IV

## 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?

1 ACCEPTED SOLUTION
Super User

Hi,

You may refer to my solution in this PBI file.

Hope this helps.

Regards,
Ashish Mathur
http://www.ashishmathur.com
6 REPLIES 6
Helper IV

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.

 ProductName Period Sales 12 MO Average of Sales Alpha 1/1/2005 0 0 Alpha 2/1/2005 0 0 Alpha 3/1/2005 0 0 Alpha 4/1/2005 0 0 Alpha 5/1/2005 0 0 Alpha 6/1/2005 0 0 Alpha 7/1/2005 0 0 Alpha 8/1/2005 0 0 Alpha 9/1/2005 0 0 Alpha 10/1/2005 0 0 Alpha 11/1/2005 0 0 Alpha 12/1/2005 0 0 Alpha 1/1/2006 0 0 Alpha 2/1/2006 0 0 Alpha 3/1/2006 0 0 Alpha 4/1/2006 0 0 Alpha 5/1/2006 0 0 Alpha 6/1/2006 0 0 Alpha 7/1/2006 0 0 Alpha 8/1/2006 0 0 Alpha 9/1/2006 0 0 Alpha 10/1/2006 27436 27436 Alpha 11/1/2006 94501 60969 Alpha 12/1/2006 83825 68587 Alpha 1/1/2007 127047 83202 Alpha 2/1/2007 128008 92163 Alpha 3/1/2007 140058 100146 Alpha 4/1/2007 133356 104890 Alpha 5/1/2007 168260 112811 Alpha 6/1/2007 176925 119935 Alpha 7/1/2007 134610 121403 Alpha 8/1/2007 167552 125598 Alpha 9/1/2007 200056 131803 Alpha 10/1/2007 111366 138797 Alpha 11/1/2007 147166 143186 Alpha 12/1/2007 115349 145813 Alpha 1/1/2008 172985 149641 Alpha 2/1/2008 222333 157501 Alpha 3/1/2008 156303 158855 Alpha 4/1/2008 160621 161127 Alpha 5/1/2008 177965 161936 Alpha 6/1/2008 178935 162103 Alpha 7/1/2008 143709 162862 Alpha 8/1/2008 244898 169307 Alpha 9/1/2008 180998 167719

Super User

Hi,

You may refer to my solution in this PBI file.

Hope this helps.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Helper IV

Worked, thanks so much!

Super User

You are welcome.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Community Support

Hi

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

Hi,

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

Regards,
Ashish Mathur
http://www.ashishmathur.com