Comparing Current Year Sales with Previous Year Average Across Month

[Taofeekat]

Can someone guide me on how to set up a pivot table to display the sales for each month in the current year, while also showing the average sales from the previous year across all months? For instance, if the average sales for last year were $200, I want to see $200 listed from January to December.

[Rohan]

Hi,

Try :

(Sum({1<Date={">=$(=yearstart(max(Date),-1)) <=$(=yearend(max(Date),-1))"}>} Sales)/12)

This should give you a fixed Avg of Previous Year.

Regards,

Rohan.

[Taofeekat]

Thank you Rohan,

I have tried this measure, i didn't get a fixed average for last year.

Am i missing something?

[Rohan · Accepted solution]

Hi,

Just a Slight Modification :

(Sum({1<Date={">=$(=yearstart(max(Date),-1)) <=$(=yearend(max(Date),-1))"}>}Total<Year> Sales)/12)

Also since you are using Year as a dimension, You will have to do slight adjustment to your data model & create a list of Years in a seperate, unlinked Table & use that dimension as year, then you will be able to get the Past Year Avg & Current year Sales in a single row.

Regards,

Rohan.

[Taofeekat]

Thank you, Rohan, this works perfectly.

I appreciate your time and patience.