Set Analysis is not working in Pivot table

[madnanansari]

I have a simple pivot table with the below columns:

Month

Sales

Sales (Last Year, Same Month)

Sales (Last Year, Same Month) has the below formula:

Sum(

{$<

[YearMonth No]= ,

[Year] =,

[Month No]=,

[Month Year]= ,

[Month]= ,

MonthID={$(=Max(MonthID)-12)}

>} Sales)

In the Model, I have developed the Master Calendar, Month, Sales & some other tables.

Month table has the below columns:

[Month Year], Year, [Month No], Month, [YearMonth No], MonthID

MonthID is calculated with the below formula:

(Year-1)* 12 + [Month No] which generates a sequence without missing nos for each month in a sequence.

[sunny_talwar]

What is the issue here? Can you explain a little bit?

[johnw]

Are you wanting a chart that looks something like this?

Jan 2015  10
Feb 2015  25
Mar 2015  30
...
Jan 2016  17  10
Feb 2016  12  25
Mar 2016  45  30
...

If so, set analysis isn't going to work, as it isn't evaluated in the context of a dimension. But I don't know what you're after.

[#4]

It easy to implement with Rolling n months calendar. Please go through the below link for reference.

Calculating rolling n-period totals, averages or other aggregations