Moving Average of Monthly Sales Based on Daily Transactions

[martinomf]

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!

Kind regards,

Martin

[shebr]

Hi @martinomf,

This may help.

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.

See image below.

Let me know how you get on,

Thanks

shebr

[martinomf]

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.

[martinomf]

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.

I will appreciate your suggestions!

Kind regards,

Martin

[iamprajot]

Create a Average Sales Measure depending on the Data for e.g.

Average Sales = AVERAGEX ( Sales, Sales[Quantity] * Sales[Unit Price] )

Average Sales = AVERAGEX ( TableName, TableName[Column1] * TableName[Column2] )

then finally,

Average Moving Sales 12 Months = CALCULATE ( [Average Sales], DATESINPERIOD ( Orders[OrderDate].[Date], LASTDATE ( 'Orders'[OrderDate].[Date] ), -12, MONTH ) )

[martinomf]

Thanks for your response, iamprajot.  Keep in mind that I have daily transactions that I need to aggregate first on a monthly total.  Then, I need to get the moving averagte of the monthly totals.

The result that your expression will give me will be the moving average of daily transactions over the last 12 months instead of the moving average of the monthly totals, correct?

[iamprajot]

It gives Monthly Average and than Moving Average of Monthly figures
Nothing day wise.

[martinomf]

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.

[iamprajot]

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)

I hope you got the point.

[martinomf]

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.

I look forward to receving your comments!

Thanks again!! 🙂

[iamprajot]

I know it is Monthly Total and not Average but the way you need what you need is conceptually wrong and results in wrong figures.
it cannot be divided by 12, the actual values needs to be divided by actual count.
suppose if there were no transactions in March month than remaining months are 11 in the pivot, and if we go your way than it will divide the Total with 11 months or it could give you the 13 months moving average.
you just need to understand the concept, it is the flaw of Pivot table. check the average using formulas and you will see.
there is nothing I could do more, it is just a logic that you need to understand.

[martinomf]

I appreciate your response and your time, Prajot.  I have tried your solution again: your expression provides me with the 12 Month Moving Average of the value of the INDIVIDUAL transactions, which is NOT what I need.  I need to have a 12 Month Moving Average of how much business, in terms of $, is brought into the business on a monthly basis, which is what the table I sent you correctly conveys.

Best regards 🙂

[iamprajot]

Here you go, finally...

CALCULATE([Total Revenue]/12,DATESINPERIOD(Orders[OrderDate].[Date],LASTDATE(Orders[OrderDate].[Date]),-12,MONTH))

in which [Total Revenue] is SUM of Revenue Column which could be another Measure or put inside above Calculate formula.

Let me know once you get what you were expecting.

[iamprajot]

Mark as Solution if it worked to help others.