Date IF functions within pivot tabels for average monthly sales

[quacks96]

I'm trying to put together a pivot tabel that allows me to see our customers monthly spend from 2018 onwards.

Which I can get to work for each year apart from 2021, becuase there's only been 5 months so far yet this is the only formula that i can get to work =Sum(total sales)/12 in calculated fields.

[#2]

@quacks96 

I assume you have Date in source data, not only years. Creating PivotTable first add data to data model. For this simple model:

If you don't have separate Month column group Date by Month and Year. Add measure for Monthly Average:

and add it to PivotTable

[#3]

@Sergei Baklan 

Thanks Sergi, the only issue is that now, I can't summarise the sales over an annual period in a monthly format, if that makes sence.

As there are customers that may purchase in January for $120 but not for the rest of the year, yet the average displays $120 over a 12 month period, which isnt correct, i would like it to display $12

[#4 · Accepted solution]

@quacks96 

So you take 12 months but current, and this month number for the current year.  Measure will be

Monthly Average:=
VAR maxDate=MAX(Sales[Date])
VAR nMonths=IF(YEAR(maxDate)=YEAR(TODAY()), MONTH(TODAY()),12)
RETURN DIVIDE(SUM([Amount]), nMonths, BLANK())

which gives

[#5]

Hi Sergei

Could you please show your formula in both the field and measurement window.
I think I've missed a component that you've done

[#6]

@quacks96 

You may open the file attached to previous post and check it in measures, it's here