Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
zflory
Helper I
Helper I

Year over Year Comparison

Hello,

 

I am currently have a matrix that displays Volume and Plant Margin by Volume. How would I be able to show a year over year comparison for these 2 categories? I have all of the data for 2016, I am just unsure of the correct measure to use

 

powerbi.PNG

17 REPLIES 17
nickchobotar
Skilled Sharer
Skilled Sharer

@zflory

 

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

 

@nickchobotar

 

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

 

powerbi2.PNG 

@zflory

 

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 -

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?

 

powerbi3.PNG

@zflory

 

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.

 

 

image.png

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 -

 

@nickchobotar 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

 

powerbi6.PNG

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

 

image.png

 

Hopefully, this was helpful.

 

Nick -

 

@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 

@zflory

 

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

 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

 

powerbi7.PNGpowerbi8.PNG

@zflory

 

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 Result

 

Did I understand you correctly? Do you want turn all numbers into negative or just turn the positive numbers into negative?

thank you for the help!

amback00
New Member

You could try the SAMEPERIODLASTYEAR function: https://msdn.microsoft.com/en-us/library/ee634972.aspx

@amback00 i tried using the SAMEPERIODLASTYEAR measure but that does not work because the parameter I want to compare is Volume or Plant Margin by Volume, not dates

thank you for the help!

Greg_Deckler
Super User
Super User

Try out the Year-over-Year Quick Measure.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Thank you for the response. Do you know where I can find that? Or how that measure is supposed to be set up?

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.