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

[chudson]

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?

[chudson]

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

[Ashish_Mathur · Accepted solution]

Hi,

You may refer to my solution in this PBI file.

Hope this helps.

[chudson]

Worked, thanks so much!

[Ashish_Mathur]

You are welcome.

[v-yuta-msft]

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

[Ashish_Mathur]

Hi,

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