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 non-time 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

Answer 1

Constraint : I am looking for a solution without paralellperiod, because this principle can be applied to a non-time 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 non-numeric variable substitute the numeric key for the variable itself. (In the example above, substitute the numeric key for [Order Year].)

Answer 2

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