Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreShape the future of the Fabric Community! Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions. Take survey.
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?
Solved! Go to Solution.
Hi,
You may refer to my solution in this PBI file.
Hope this helps.
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 |
Hi,
You may refer to my solution in this PBI file.
Hope this helps.
Worked, thanks so much!
You are welcome.
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
Hi,
Share a dataset and show the expected result in a Table form.
User | Count |
---|---|
93 | |
92 | |
85 | |
83 | |
49 |
User | Count |
---|---|
150 | |
142 | |
112 | |
73 | |
55 |