Hi
Hoping someone can shed some light on a calculation issue i have.
The scenario is i want to be able to calculate the % of change positive or negative comparing two values last yeat versus this year.
Eg This Year the value is 2, Last year the value was 0, so there is a 2 point difference yoy, so how do i calculate this as a percentage?.
I have been trying to get some dax calculations to work for me. See the table below. But i dont think i have the % difference calculation working properly as the result for where the value is 2 for this year and 0 for last is producing a 0% yoy difference result when applying my calculation.
My calculations in the table are structured as follows
(VAR)Total Serious Incidents =
VAR SIValue =
CALCULATE([Total Serious Incidents])
RETURN
COALESCE(SIValue,0).
Serious Incidents LY (VAR) =
VAR SILYValue =
CALCULATE([Total Serious Incidents],SAMEPERIODLASTYEAR(Dates[Date]))
RETURN
COALESCE(SILYValue,0)
Difference Serious Incidents LY =
IF(ISBLANK([Serious Incidents LY (VAR)]),BLANK(),[(VAR)Total Serious Incidents]-[Serious Incidents LY (VAR)])
% Diff LY Serious Incidents = DIVIDE([Difference Serious Incidents LY],[Serious Incidents LY (VAR)],0)
so how do you want the logic to work on paper:
in your example
"This Year the value is 2, Last year the value was 0, so there is a 2 point difference "
since last year it was 0 and this year it is 2 the percentage increase is infinity.
what value would you want to show… infact how will you like the issue to be handled when there is 0 incident last year but some incident in current year.
This is what google says
“There is no rate of growth from 0 to any other number. That is to say, there is no percentage of increase from zero to greater than zero and there is no percentage of decrease from zero to less than zero (a negative number)”
should not you display Blank() or [NaN] or [N/A] in such cases?
Hi
So then is calculation correct but not reflecting in the scenario i am wanting present.
What i am after is the positive or negative variance showing as a percentage based on last years results. So where there has been an increase or a decrease in the value this year compared to last year what is percentage of change
it seems to be working where this year has a 0 value and last year has a number value e.g 2 which is showing in the image below, the percentage difference is calculating the results -100%
But where there is 0 in the last year column and a value in the current year then the % difference calculation is 0%. So my total percentage change is not correct.
gosh i hope i am making some sense
Hello @AoakeP -
i think you are going down a rabbit hole 
.
when you drop from a number to 0 it means you lost everything that is 100% lost … so in your e.g. if you have 2 points last year and this year you have 0 point % decrease = change divided by last year value or -2/2 * 100 = -100% drop
however when the base value is 0 you cannot say how much the increase has been… e…g you have $0 in your pocket last year and $100 this year you cannot express the $100 gain as a percentage increase. go with “N/A” in such cases.
I agree thanks I have looked over the calculation again and it is rolling up the total % change at the company level correctly.
Thanks for your help.