Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
chudson
Helper IV
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?

 

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
chudson
Helper IV
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.

 

 

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/

Worked, thanks so much!

You are welcome.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
v-yuta-msft
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

Ashish_Mathur
Super User
Super User

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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors