Forum Discussion
Helper IYear over Year Comparison
Skilled SharerHi zflory
The reason you are having negative numbers is because your 2017 aggragated volume for some items is less than your 2016.
(which makes perfect sense since 2017 is not over yet. Technically, you are comparing 12 months of 2016 with almost 11 months of 2017
Hopefully, this was helpful.
Nick -
Helper Inickchobotar thank you again for your assistance. Is there a way to do a YTD comparison? Like if I only want to do a comparison of the data from YTD-september of 2016 and 2017. I would rather not have a full 12 months worth of data from 2016 if I'm only comparing through September 2017.
Again thank you for all of your help
- nickchobotar
With your data this is not fully possible. We need dates or months and you have neither. So, the only thing I can think of is to use a year fraction.
What I am saying below is calculate year fraction of 2017 YTD which is about 80% and them use it to adjust your 2016 volumes by the same percentage. Now, you should be comparing apples to apples.
YoY Value = VAR YearFraction = YEARFRAC(DATE(YEAR(TODAY()), 1, 1), TODAY()) // Get Year fraction for 2017 VAR Value2016 = CALCULATE ( SUM ( Combined[One ] ) * YearFraction , Combined[Year] = 2016 ) // adjust 2016 volumes to 2017 through year fraction adjusting factor VAR Value2017 = CALCULATE ( SUM ( Combined[One ] ), Combined[Year] = 2017 ) RETURN Value2017 - Value2016- Nick
- zflory
thank you nickchobotar. Can you see the below volumes? The difference between them is shown on the right (25966). Is there any way to display negative values for the 2017 data - 2016 data? I want it to show -25966, not 25966
- nickchobotar
If I am understanding correctly, you are trying to force your numbers to be negative.
If so, then you can use a minus in front of your numbers wrapped in absolute function, as shown below:
YoY Value = VAR YearFraction = YEARFRAC(DATE(YEAR(TODAY()), 1, 1), TODAY()) // Get Year fraction for 2017 VAR Value2016 = CALCULATE ( SUM ( Combined[One ] ) * YearFraction , Combined[Year] = 2016 ) // adjust 2016 volumes to 2017 through year fraction adjusting factor VAR Value2017 = CALCULATE ( SUM ( Combined[One ] ), Combined[Year] = 2017 )
VAR Result = - (ABS(Value2017) - ABS(Value2016)) RETURN ResultDid I understand you correctly? Do you want turn all numbers into negative or just turn the positive numbers into negative?