Forum Discussion
YOY DAX Measure
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 |
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.
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
This is perfect. You can use the IFerror function to take care of exceptions.
CheenuSing
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
7 Replies
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
Hello Anonymous, 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.
This is perfect. You can use the IFerror function to take care of exceptions.
CheenuSing
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]) )
