cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

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!

Kind regards,

Martin

13 REPLIES 13
Highlighted
Resolver III
Resolver III

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

 

relative filter.JPG

Highlighted

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.

Highlighted

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

Highlighted

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 ) )

 

 

 

Highlighted

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?

Highlighted

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

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.

 

Sample Power BI.JPG

Highlighted

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.

Highlighted

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!! 🙂

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors