Date IF functions within pivot tabels for average monthly sales

New Contributor

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.



5 Replies


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




@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





best response confirmed by quacks96 (New Contributor)


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


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


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