Answered by:
Computing percentage of evolution : powerpivot challenge ! previousmember/currember in DAX ?
Question

Dear All,
I am trying to compute percentage of evolution.
Please find based in northwind db example the following screenshot :
I defined obviously :
unitPrice_dax:='Order Details'[Sum of Sales]/'Order Details'[Sum of Quantity]
then I want to compute a percentage of evolution as ([this year]  [last year])/[last year], so I defined
%_price_evol_dax:=(calculate ([unitPrice_dax],'Orders'[Order Year] = 1997)calculate ([unitPrice_dax],'Orders'[Order Year] = 1996))/calculate ([unitPrice_dax],'Orders'[Order Year] = 1996)
My questions :
Of course this is static, I want to have with %_price_evol_dax, 1998 vc 1997 when order year currentmember it is 1998, 1997 vs 1996 when is 1996 , and nothing when it is 1996 ?
Constraint : I am looking for a solution without paralellperiod, because this principle can be applied to a nontime variable.
My initial idea = I was looking for prevmember like function or sibling function as we have in MDX ?
Any ideas ?
BR,
RenoH  Paris  France
Monday, April 16, 2012 5:59 AM
Answers

Constraint : I am looking for a solution without paralellperiod, because this principle can be applied to a nontime variable.
I think your solution will require you to create a numeric key representing the variable that you want showing as rows, and then reference the previous member by subtracting 1 from the current member.
E.g.
IF(HASONEVALUE(Orders[Order Year]), ([UnitPrice]CALCULATE([UnitPrice, Orders[Order Year]=VALUES(Orders[Order Year])1)) /CALCULATE([UnitPrice, Orders[Order Year]=VALUES(Orders[Order Year])1), BLANK() )
For a nonnumeric variable substitute the numeric key for the variable itself. (In the example above, substitute the numeric key for [Order Year].)
 Marked as answer by RenoH Monday, April 16, 2012 10:06 AM
Monday, April 16, 2012 6:24 AM
All replies

Constraint : I am looking for a solution without paralellperiod, because this principle can be applied to a nontime variable.
I think your solution will require you to create a numeric key representing the variable that you want showing as rows, and then reference the previous member by subtracting 1 from the current member.
E.g.
IF(HASONEVALUE(Orders[Order Year]), ([UnitPrice]CALCULATE([UnitPrice, Orders[Order Year]=VALUES(Orders[Order Year])1)) /CALCULATE([UnitPrice, Orders[Order Year]=VALUES(Orders[Order Year])1), BLANK() )
For a nonnumeric variable substitute the numeric key for the variable itself. (In the example above, substitute the numeric key for [Order Year].)
 Marked as answer by RenoH Monday, April 16, 2012 10:06 AM
Monday, April 16, 2012 6:24 AM 
Hi,
Many thanks Ruve1k
I applied your idea and it sounds good.
I come form SSAS BI so I try to reproduce my business cases in power pivot, stay tuned I am preparing.
BR,
RenoH  Paris  France
Monday, April 16, 2012 7:02 AM