none
Computing percentage of evolution : powerpivot challenge ! previousmember/currember in DAX ?

    Pergunta

  • 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

    segunda-feira, 16 de abril de 2012 05:59

Respostas

  • 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].)

    • Marcado como Resposta RenoH segunda-feira, 16 de abril de 2012 10:06
    segunda-feira, 16 de abril de 2012 06:24

Todas as Respostas

  • 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].)

    • Marcado como Resposta RenoH segunda-feira, 16 de abril de 2012 10:06
    segunda-feira, 16 de abril de 2012 06:24
  • 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

    segunda-feira, 16 de abril de 2012 07:02