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.