YOY DAX Measure

[bajimmy1983]

Hello again community. How are you?

Thank all that review my posts and have been helped.

Now I need to know why I have this scenario. Maybe I need to complement my Measure with some trick or change it completely.

Objective: Show YOY Growth percent without "Hundred part", just the real Increase or decrease percent.

Measures I am using:

Prev Year Billing:=CALCULATE(SUM(MiddleOffice16_17[VALUE]);SAMEPERIODLASTYEAR(MiddleOffice16_17[MONTH_CORRECTED]))

YOY Billing Growth:=CALCULATE(SUM(MiddleOffice16_17[VALUE]))-[Prev Year Billing]

YOY Billing Perc Growth:=DIVIDE([YOY Billing Growth];[Prev Year Billing];0)

Correct Calculation, but WRONG visualization:

BILLING MO 2016	BILLING MO 2017	YOY Billing Perc Growth
28.424.660	32.624.662	114,78%

Obs: I want to show just 14,78% which represents the increase.

Wrong Calculation Ex 1:

CLIENT	BILLING MO 2016	BILLING MO 2017	YOY Billing Perc Growth
DECATHLON	37.972	37.972	100,00%

Obs: The values are the same. So I did not increase.

Wrong Calculation Ex 2:

BILLING MO 2016	BILLING MO 2017	YOY Billing Perc Growth
	4.200.000	0,00%

Obs: Increase is 100%, but I cannot show this.

Thank you so much again.

[CheenuSing · Accepted solution]

@bajimmy1983

Hi try the following as YOY% measure

YOY% = Calculate (If(isblank([Prev Year Billing]),[SUM(MiddleOffice16_17[VALUE])]/[SUM(MiddleOffice16_17[VALUE])],([SUM(MiddleOffice16_17[VALUE])]-[Prev Year Billing])/[Prev Year Billing]) )

And define this as type Percentage in the Modelling tab.

If this solves your issue please accpet this as Solution and also give KUDOS.

Cheers 

CheenuSing

[CheenuSing · Accepted solution]

@bajimmy1983

This is perfect. You can use the IFerror function to take care of exceptions.

CheenuSing

[manojp512]

YOY% = Calculate (If(isblank([Prev Year Billing]),[SUM(MiddleOffice16_17[VALUE])]/[SUM(MiddleOffice16_17[VALUE])],([SUM(MiddleOffice16_17[VALUE])]-[Prev Year Billing])/[Prev Year Billing]) )

aprtacitizen

[Anonymous]

YOY Billing Perc Growth:=DIVIDE([YOY Billing Growth];[Prev Year Billing];0)-1 is the answer

[CheenuSing · Accepted solution]

@bajimmy1983

Hi try the following as YOY% measure

YOY% = Calculate (If(isblank([Prev Year Billing]),[SUM(MiddleOffice16_17[VALUE])]/[SUM(MiddleOffice16_17[VALUE])],([SUM(MiddleOffice16_17[VALUE])]-[Prev Year Billing])/[Prev Year Billing]) )

And define this as type Percentage in the Modelling tab.

If this solves your issue please accpet this as Solution and also give KUDOS.

Cheers 

CheenuSing

[bajimmy1983]

Hello @CheenuSing, How are you?

First, thank you so much for your fast reply. I could see your suggestion just now (Sunday, Oct, 09 - 2:37pm Brazil time).

I think we are almost there with your DAX formula. I just got one error in one line of Pivot Table and I complemented the formula like bellow (I do not know if this is the best approach to do so).

Actual Calculate Field:

YOY%2:=CALCULATE(IFERROR(IF(ISBLANK([Prev Year Billing]);SUM(MiddleOffice16_17[VALUE])/SUM(MiddleOffice16_17[VALUE]);(SUM(MiddleOffice16_17[VALUE])-[Prev Year Billing])/[Prev Year Billing]);0))

Why I am using IFERROR function? Because of this:

SPECIALTY	CLIENT	BILLING MO 2016	BILLING MO 2017	YOY% Error
ECSELIS			0	#NÚM!
	NEW BUSINESS		0	#NÚM!
Total Geral			0	#NÚM!

Please check if my approach is the best and if you can suggest another better way. If my complement is right, I understand we can set this case as resolved, right?

Thank you again.

[CheenuSing · Accepted solution]

@bajimmy1983

This is perfect. You can use the IFerror function to take care of exceptions.

CheenuSing

[bajimmy1983]

@CheenuSing, 

I forgot to mention about showing just the real part of increase (15,8%) and not 115,8%. Do you know how?

And taking a further analysis, I have as bellow using actual calculated field. This is not correct, right? Because in this case I understand we did not increase. I will consider an increase of X% when 2017 Billing is higher than 2016 Billing.

BILLING MO 2016	BILLING MO 2017	YOY% Error
164.624	164.624	100,0%

[CheenuSing · Accepted solution]

@bajimmy1983

If you have made the YOY%error as data type and have used the formula (Current Year - Previous Year) / (current Year) you will always the incremental percentageonly and not 100 + incremental percentage.

This happens when your formula is [CurrentYear] / [PreviousYear]

Hope this clarifies

CheenuSing