Moving Average of Monthly Sales Based on Daily Transactions
Good morning! I am new to Power BI and I have been stuck with a problem for a few days now. Hope someone can help me solve it! I have a data set of daily sales for some years. I need to get the total sales per month and then calculate 12 month moving averages of those monthy totals. I will appreciate your suggestions!
First of all create 2 measures, one "sum sales = sum([sales])", and a second "average sales = AVERAGE([sales])"
Place these two on the 'Line and clustered column chart'. Place the [sum sales] in the column values and the [average sales] on the line values.
Drag the same date (yes, again even though its already there) into the filter area of the chart, and use 'Relative Filtering', select 12, and then 'Months'. This will always give you the last 12 months based on the current date.
Thanks for your response, shebr. Your solution, as proposed, does not solve my problem. I need to create a DAX expression that works dynamically and that can work both in tables and visualizations and that can also be replicated in calculations made for other ratios.
Good morning! I am new to Power BI and I have been stuck with a problem for a few days now. Hope someone can help me solve it! I have a data set of individual sales transactions on a daily basis that goes back for some years. I need to get the total sales per month and then calculate a 12 month moving averages of those monthy totals.
Using a combination of CALCULATE, AVERAGE and DATESINPERIOD will provide me with a moving average of 12 months but of the individual transactions instead of the moving average of the monthly totals.
Thanks again, Iamprajot. On the left you will find a sample of the data, on a daily and monthly basis. On the right, you will find a pivot that shows what I need to do on Power BI; a monthly total and a 12 month moving average of the monthly numbers. What you are kindly giving me is not working.
Your Pivot is giving WRONG Averages. read & understand this very carefully, Right Pivot is adding 12 months Revenue and dividing it with 12 (which is wrong because those values are already summarised and division with 12 is wrong) now do this, filter the Left data for required 12 months and then check the Average of those Revenues (it will add all those and divide it with the count, which is right and this is what Power BI does)
Thanks again, Prajot. If you take a look to the second column of the pivo, the first result ( 2015 Jan for 2,014,831) is the result of the sum of all January 2015 transactions (348,326 + 0 + 1'666,506). The same goes for 1,178 744 which is the result of summing the Feb 2015 transactions (219,277 + 604,238 + 355,229) and so on. They are not averages, they are monthly totals.
The first 12M Moving Avg. (1,928,576) is the average of the sums that go from Feb 2015 to Jan 2016 itself. This is the first sum and division by 12; there is no previous one. This is exactly what I need Power BI to do for me. Hope this explanation makes things clearer.