0 Replies Latest reply: Oct 27, 2016 4:01 AM by Mohammad Adnan Ansari RSS

    Set Analysis is not working in Pivot table

    Mohammad Adnan Ansari

      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.