time intelligence and combing measures

[srdirect]

HI ALL in my workbook I have many years of data, (in one table) I also have a Table of all dates (for the next 10 years or so)



what I am trying to do is have the last 12 months of data plus 2 more measures (a) giving a sum of YTY measure and (b) aggregating last 3 months

such that report will look like


-| 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | 11 | 12 | YTY | 3 Months
rows


is their a way to combine the data fields such that it shows this way?

thanks in advance...

 

[idan_Excelando]

What you are trying to do, breaks the pivot sets logic. You can do this with either named sets or cube formulas, Here is a starting point for the named sets option:
Named Sets and “Asymmetric” Pivots: Showing Different Measures for Different Years « PowerPivotPro

basically, after you created the 3 measures, you will need to remove the months set for the last two measures using a named Set

 

[scottsen]

The other way to do this is via a disconnected table. If you have a table with a column holding "1", "2", ..."12", "YTY", "3 Mo"... you could put it on columns.

You then write a wacky measure which detects which column you are on...
=IF (VALUES(MyTable[Wacky]) = "1", [1 Month Ago Measure],
IF (VALUES(MyTable[Wacky]) = "YTY", [YTY]
...