DAX (measure) Year Amount Comparison in % (increase and decrease)

[bajimmy1983]

Hello all, good afternoon!

I just registered myself in this community. I am a newbie in DAX, but trying to apply as much as possible some daily financial situations I already have. "I think when you have an enviroment at your disposal, this is the best way to put in practice what you are learning and advance your abilities."

So, I am trying to create a measure (according to "Power Pivot and Power BI" book this is the best way and I am trying to avoid Column calculation approach) that will compare all investment BY [PRODUCTS] from 2015 and 2016 in % (both increase and decrease % numbers). 

Table Name = 'PILOT';

Column 'PILOT'[YEAR] = 2015 & 2016 values;

Column 'PILOT'[INVESTMENT AMT] = Amounts in BRL; 

Column 'PILOT'[PRODUCTS] = All products

Objective: Compare 2015 and 2016 in % to show in a Pivot Table (Power Pivot) increase and decrease scenarios; 

Problem found: There are some products that does not have any investment on 2015 and does in 2016. So, the formula has to be dynamically enough to recognize this and show 100% increase and vise versa (-100%). 

I do not know how to accomplish this result using DAX in calculated field (Measure). I have tried DIVIDE function, but failed.

Thank you all in advance,

Jaderson Almeida (Jimmy)

[PavelR · Accepted solution]

Hi @bajimmy1983 is the picture below the result that you want?

Regards

Pavel

[PavelR · Accepted solution]

Hi @bajimmy1983 is the picture below the result that you want?

Regards

Pavel

[bajimmy1983]

Hello Pavel,

YES!! How could you accomplish it? Could you share the DAX formula?

Thanks again!

[PavelR · Accepted solution]

Yeah, of course @bajimmy1983! I just created these three dax calculations:

2015 = CALCULATE(SUM(PILOT[INVESTMENT AMT]);PILOT[YEAR]=2015)

2016 = CALCULATE(SUM(PILOT[INVESTMENT AMT]);PILOT[YEAR]=2016)

YOY(%) = IF(OR(ISBLANK([2015])=TRUE();ISBLANK([2016])=TRUE());BLANK();DIVIDE([2016];[2015])-1) - format percentage with one decimal place

Is it clear? 

You are welcome Jimmy.

Regards.

Pavel

[bajimmy1983]

Pavel,

Perfect solution 🙂 thank you so much once again. I hope one day I can help others like you did.

[Shanebenedict]

Is there no embedded formula which could help obtain the same result without having to type the DAX measure. Its Microsoft i am sure you guys can create a code which will help formulate that automatically.