SOLVED

New Contributor

# Date IF functions within pivot tabels for average monthly sales

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

# Re: Date IF functions within pivot tabels for average monthly sales

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:

# Re: Date IF functions within pivot tabels for average monthly sales

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

# Re: Date IF functions within pivot tabels for average monthly sales

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

# Re: Date IF functions within pivot tabels for average monthly sales

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

# Re: Date IF functions within pivot tabels for average monthly sales

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