Answered by:
Create a new member on dimension : MDX vs DAX ?

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] :
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
 Edited by RenoH Monday, April 16, 2012 7:20 AM
Question
Answers

you cannot create new members in DAX
you can only create new calculations
there is only one option you may use:
use a DateToolDimension and apply a calculation based on the currently selected item of this DateToolDimension
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 
 Marked as answer by RenoH Tuesday, April 17, 2012 2:18 AM
All replies

you cannot create new members in DAX
you can only create new calculations
there is only one option you may use:
use a DateToolDimension and apply a calculation based on the currently selected item of this DateToolDimension
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 
 Marked as answer by RenoH Tuesday, April 17, 2012 2:18 AM

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. Rightclick 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.)