cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
zflory Regular Visitor
Regular Visitor

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
Super User
Super User

Re: Year over Year Comparison

Try out the Year-over-Year Quick Measure.


I have book! Learn Power BI from Packt


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

zflory Regular Visitor
Regular Visitor

Re: Year over Year Comparison

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

amback00
Advisor

Re: Year over Year Comparison

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

nickchobotar Established Member
Established Member

Re: Year over Year Comparison

@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

 

Highlighted
zflory Regular Visitor
Regular Visitor

Re: Year over Year Comparison

thank you for the help!

zflory Regular Visitor
Regular Visitor

Re: Year over Year Comparison

thank you for the help!

zflory Regular Visitor
Regular Visitor

Re: Year over Year Comparison

@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

zflory Regular Visitor
Regular Visitor

Re: Year over Year Comparison

@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 

nickchobotar Established Member
Established Member

Re: Year over Year Comparison

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

Helpful resources

Announcements
Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Virtual Launch Event

Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 288 members 2,970 guests
Please welcome our newest community members: