Forum Discussion
Helper IYear over Year Comparison
Skilled Sharer
Interesting dataset. You still need one table, no point in have two separate tables with the same field and data types.
I recommend to add a Year field to each table. 2016 table gets 2016 value for every row and 2017 table get 2017 value for every row.
You can do this in Power Query
Under the Add Column tab, click Custom Column and add "=2016" this will add column with 2016 value for every row in your 2016 table. One the home tab, under transform please convert the new Year column to a whole number
Repeat the same operation for 2017 year.
Create a new blank query and enter the following code = Table.Combine({#"2017", #"2016"}). This will create a new table which is a union of 2016 and 2017. Click Close and Apply to move your changes into the model.
Now, you can use your newly created field Year as slicer and write DAX to get that YoY metric.
Something like this should work
YoY Value = VAR Value2016 = CALCULATE(SUM(Combined[One ]), Combined[Year] = 2016) VAR Value2017 = CALCULATE(SUM(Combined[One ]), Combined[Year] = 2017) RETURN Value2017 - Value2016
I would recommend to capture dates in your system for your trnasactions too. Not just years.
N -
Helper Inickchobotar thank you, this helped immensely. Is there a way to show negative numbers using this measure? Some of my year to year comparisons are negative but all of the numbers this measure returned are positive
- nickchobotar
Hi 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 -
- zflory
nickchobotar 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