SOLVED

Date IF functions within pivot tabels for average monthly sales

%3CLINGO-SUB%20id%3D%22lingo-sub-2407151%22%20slang%3D%22en-US%22%3EDate%20IF%20functions%20within%20pivot%20tabels%20for%20average%20monthly%20sales%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2407151%22%20slang%3D%22en-US%22%3E%3CP%3EI'm%20trying%20to%20put%20together%20a%20pivot%20tabel%20that%20allows%20me%20to%20see%20our%20customers%20monthly%20spend%20from%202018%20onwards.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhich%20I%20can%20get%20to%20work%20for%20each%20year%20apart%20from%202021%2C%20becuase%20there's%20only%20been%205%20months%20so%20far%20yet%20this%20is%20the%20only%20formula%20that%20i%20can%20get%20to%20work%20%3DSum(total%20sales)%2F12%20in%20calculated%20fields.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2407151%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EBI%20%26amp%3B%20Data%20Analysis%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2410081%22%20slang%3D%22en-US%22%3ERe%3A%20Date%20IF%20functions%20within%20pivot%20tabels%20for%20average%20monthly%20sales%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2410081%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1069074%22%20target%3D%22_blank%22%3E%40quacks96%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EI%20assume%20you%20have%20Date%20in%20source%20data%2C%20not%20only%20years.%20Creating%20PivotTable%20first%20add%20data%20to%20data%20model.%20For%20this%20simple%20model%3A%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20557px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F285682i13A5581E152310F3%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3EIf%20you%20don't%20have%20separate%20Month%20column%20group%20Date%20by%20Month%20and%20Year.%20Add%20measure%20for%20Monthly%20Average%3A%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20398px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F285683i3796611DF94C11E4%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3Eand%20add%20it%20to%20PivotTable%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20497px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F285684iE462BA7010D1A097%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
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