Jun 02 2021 01:04 AM
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.
Jun 02 2021 01:54 PM
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
Jun 02 2021 06:54 PM
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
Jun 03 2021 02:20 AM
SolutionSo 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
Jun 14 2021 09:43 PM
Jun 15 2021 03:09 AM
Jun 03 2021 02:20 AM
SolutionSo 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