Forum Discussion
Helper IYear over Year Comparison
Skilled Sharer
I like writing my YOY metrics all in one measure with variables.
YoY Volume = VAR Volume = SUM(Table1[Volume]) VAR VolumeLastYear = CALCULATE(Volume, DATEADD(Date[Date], -1, YEAR)) RETURN Volume - VolumeLastYear Same for the % ratio
YoY Volume% = VAR Volume = SUM(Table1[Volume]) VAR VolumeLastYear = CALCULATE(Volume, DATEADD(Date[Date], -1, YEAR)) YOY_Volume = Volume - VolumeLastYear RETURN DIVIDE(YOY_Volume, VolumeLastYear)
- N
Helper I
VAR VolumeLastYear = CALCULATE(Volume, DATEADD(Date[Date], -1, YEAR))
for the portion DATEADD(Date[Date], what if I am not adding a Date column into the [Date] portion? My data is on 2 different sheets, one for 2017 and one for 2016. I was able to set up the Var Volume for 2017, but I need to show a comparison and I do not have a "Date" column on either one of my excel sheets
- nickchobotar
Based on your question I am assuming you are not using a Date table/dimension in your model. You need this table.
The fastest way is to create an automatic date table.
Step 1: Go to Modeling - New Table - enter the following:
= CALENDARAUTO()
Step 2: Go to relationships tab (three squares icon) and create a relationship from your newly created date table with the table where you have dates - Table1 (see below 2016+2017 table)
You do not need two separate tables just to distinguish between two years, so you need to combine your 2016 and 2017 data into one table. Again. the fastest way is to create a new table.
Step 3: Go to Modeling - New Table - enter the following:
Table1=UNION(2016, 2017)
*** I am assuming you have a date field in 2016 & 2017 tables
**** Table1 is the name I used in my measures. If you change it, make the same chages in the measures too.
N -
- zflory
Thank you for the reply again nickchobotar, but I do not have a date column in either of my 2016 or 2017 tables. Do those need to be added?
- nickchobotar
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 workYoY 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 -