Forum Discussion
YOY DAX Measure
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
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
Anonymous,
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% 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