cancel
Showing results for
Did you mean:
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
Responsive Resident
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.
Highlighted
Frequent Visitor

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 🙂

Highlighted
Responsive Resident

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.

Highlighted
Responsive Resident
Mark as Solution if it worked to help others.

Announcements

Power Platform Community Conference

Check out the on demand sessions that are available now!

Microsoft Power Platform Communities

Check out the Winners!

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors