Create a new member on dimension : MDX vs DAX ?

Question

Dear All,

Following my previous post, let's dig a bit. I have the following table :

I want to do comparison between years.

I got part of the solution thanks to Ruve1k [+1] :

http://social.msdn.microsoft.com/Forums/en-US/sqlkjpowerpivotforexcel/thread/766cb82c-4101-4895-898f-04088bcfe438

the only problem is that I 'll to define as many "Evolution of ..." variables as I have measures (for example margin, margin rates etc  ....).

In a cube, I would have used MDX in order to create a new member on my "peridodical dimension" as follow :

with 
     Member [Order Date].[Time Hier].[Evolution]
     as 
     (
       ([Order Date].[Time Hier].[Year].[1998] - [Order Date].[Time Hier].[Year].&[1997])
       /
       [Order Date].[Time Hier].[Year].&[1997]
      ) ,     FORMAT_STRING = 'Percent'
       

In a cube, this would have added to new member named  "Evolution" applied to any measures mentioned bellow.

Such member will appear  on the right of "Current Year "with the following result :

Is it possible to define a new member on a categy as i do in MDX to avoid to define many measures (maintenance & error risk ) ?

BR to all,

---

RenoH - Paris - France

Answer 1

you cannot create new members in DAX

you can only create new calculations

there is only one option you may use:
use a DateTool-Dimension and apply a calculation based on the currently selected item of this DateTool-Dimension
this is also done in the Contos Sample Workbook:

=IF( COUNTROWS( VALUES( DimPeriod[Period]))=1;
  IF( VALUES( DimPeriod[Period]) = "Current"; [Sales];
  IF( VALUES( DimPeriod[Period]) = "MTD"; [Sales](DATESMTD(DimDate[Datekey]));
  IF( VALUES( DimPeriod[Period]) = "QTD"; [Sales](DATESQTD(DimDate[Datekey]));
  IF( VALUES( DimPeriod[Period]) = "YTD"; [Sales](DATESYTD(DimDate[Datekey]));
  IF( VALUES( DimPeriod[Period]) = "LastYear"; [Sales](DATEADD(DimDate[Datekey];-1;YEAR));
  IF( VALUES( DimPeriod[Period]) = "PriorYearMTD"; [Sales](DATEADD(DATESMTD(DimDate[Datekey]);-1;YEAR));
  IF( VALUES( DimPeriod[Period]) = "PriorYearQTD"; [Sales](DATEADD(DATESQTD(DimDate[Datekey]);-1;YEAR));
  IF( VALUES( DimPeriod[Period]) = "PriorYearYTD"; [Sales](DATEADD(DATESYTD(DimDate[Datekey]);-1;YEAR));
  BLANK()))))))));[Sales])

hth,
gerhard

---

- www.pmOne.com -

Answer 2

RenoH,

While not really a PowerPivot solution per se, you can add a session calculated member to your pivot table by using a tool like OLAP Pivot Table Extensions. Essentially, it allows you to add the MDX that you listed above to the beginning of the MDX query that your pivot table generates against the embedded PowerPivot cube. Note that nothing gets added to your model. It is simply the front end tool (Excel) that enables this functionality.

You may also consider Excel's built in functionality. Right-click on any measure, then choose: Show Values as... > % Difference From... . Then set Base Field to Order Year, and Base Item to (previous). This will give you the percentage growth compared to the previous member. (What you call Evolution.)