4 Replies Latest reply on Jun 12, 2018 7:59 AM by Naveen B

# Monthly YTD average calculation

Hi,

Hoping someone can help me with this one ................

I want to create a field that will calculate a rolling monthly YTD average figure e.g.

Jan = 10

Feb = 20

Mar = 30

Average for Jan would be 10; average for Feb would be 15 [(10+20)/2]; average for Mar would be 13.33 [(10+20+30)/3] and so on.

I have been trying a couple of options but think I'm missing a step ... can anyone advise?

Thanks,

Ben

• ###### 1. Re: Monthly YTD average calculation

Look into use  a running total table calculation Keep the measure value as sum([value]), but then right click on this measure, add table calculation, calculation type = running total, using average.

Let me know if I need to post screen shots.

• ###### 2. Re: Monthly YTD average calculation

Hi Ben,

It depends on how your data is actually stored and how you're planning to display this. If it's only monthly level rows and you're only showing the average on a sheet (not including monthly level rows), then you should just be able to do:

SUM([Measure]) / COUNTD([Date])

If you have multiple dates per month, you could alter this to:

SUM([Measure]) / COUNTD(DATETRUNC("month", [Date]))

Difficult to say what else might be needed without seeing some data or a workbook, but hopefully this can at least get you started!

Best,

Bryce

• ###### 3. Re: Monthly YTD average calculation

Good morning Ben

see the attached

You didn't include a twbx workbook s I used superstore data that you can adapt

this formula will calculate the moving avg from the first of the year to the month in the record

it is set to restart every year

Jim

If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.

• ###### 4. Re: Monthly YTD average calculation

Hi Ben,

I have taken the Sample superstore data to analyze your issue. Kindly Follow the below steps to achieve the solution

1) I have Created a View Month Wise Sales

2) Drag the Sum(Sales) again to the view and Select the table calculation as Moving Average

3) You Can See the YTD Average as below

January - 94925 , Moving Average: 94925

February - 59751, Moving Average : (94925+59751)/2=77338

March - 205005, Moving Average : (94925+59751+205005) = 119894

Hope this Helps, Kindly mark this question as Correct answers if it resolves your issue.