Non-Weighted Running Average

[Anonymous]

Hello friends.

I have a Fact table of sales with Price and Cost = Profit

I have a disconnected Date Table.

I have a calculated AverageProfit Measure that for any given filter calculates the Average Profit for all the transactions.

I need to build a Non-Weighted 6 months Running Average that for any chosen Month (from disconnected Date Table) will calculate the simple average of the AverageProfits of the 6 preceding months.

Let's say AverageProfit for: January - 20%, Feb - 30% Mar - 40% Apr - 35% May - 25% June - 30%

I need the new measure to show 30% for June, notwithstanding the number of sales in each month.

Thank you!

Michael

[GilbertQ · Accepted solution]

Hi @Anonymous

I am going to assume that you have got a date in your Fact table?

If you do, and you have got the May version of Power BI Desktop you can then right click on your Fact table and select Quick measures.

One of the options is shown below to create a Rolling Average as you require.

Once this is created, you can then see the DAX formula which you can then use going forward.