SOLVED

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

@quacks96 

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

image.png

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

image.png

and add it to PivotTable

image.png

 

 

@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)
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

image.png

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

@quacks96 

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

image.png