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

Compare values by year in PowerBI Desktop

Hello,

 

I want to compare values from different periods (years). I want in Line Chart report to compare the values from different years month by month. The important columns are Date and Q-ty.

My data is:

 example.png

 

I want to compare these last 3 years in Line Chart or any other which is suitable. The data in lines is just to show how it should looks it is not with real data.  I have also a DATE table, if it will be needed.

 

Thanks in advance!

 

 

2 ACCEPTED SOLUTIONS

Accepted Solutions
shill1000 Member
Member

Re: Compare values by year in PowerBI Desktop

Don't know if it is too simplistic, but if you parse the date as 2 added columns (one for year and one for month), then create a line chart with Month as the Axis, Year as the Legend and Quantity as the Value, doesn't that give the effect you are looking for? I didn't add a measure to smooth out the charts, just did the basics and got this:

 

Sample year line chart.JPG

View solution in original post

SivaMani Senior Member
Senior Member

Re: Compare values by year in PowerBI Desktop

Hi @ultrashock,

 

I have created two measure based on what I understand from you,

  1. PYR - previous year Qty
  2. TWOYRBCK = Qty 2 years ago

See the gif,

Last 3 year.gif

 

I hope I made it clear.

 

Regards,

Siva

View solution in original post

5 REPLIES 5
SivaMani Senior Member
Senior Member

Re: Compare values by year in PowerBI Desktop

@ultrashock,

 

Have you tried calculated columns?

ultrashock Regular Visitor
Regular Visitor

Re: Compare values by year in PowerBI Desktop

Yes , I have tried columns with YEAR() and MONTH(), but I don't know how to compae values from different years on one chart.

shill1000 Member
Member

Re: Compare values by year in PowerBI Desktop

Don't know if it is too simplistic, but if you parse the date as 2 added columns (one for year and one for month), then create a line chart with Month as the Axis, Year as the Legend and Quantity as the Value, doesn't that give the effect you are looking for? I didn't add a measure to smooth out the charts, just did the basics and got this:

 

Sample year line chart.JPG

View solution in original post

SivaMani Senior Member
Senior Member

Re: Compare values by year in PowerBI Desktop

Hi @ultrashock,

 

I have created two measure based on what I understand from you,

  1. PYR - previous year Qty
  2. TWOYRBCK = Qty 2 years ago

See the gif,

Last 3 year.gif

 

I hope I made it clear.

 

Regards,

Siva

View solution in original post

ultrashock Regular Visitor
Regular Visitor

Re: Compare values by year in PowerBI Desktop

Hello,

 

Thanks a lot fort he quick answers. I have tried both methods and both of them worked. I have chosen the first one by shill1000, because it is simpler for manage. 

The only difference is that I also have a Date table (the query for the date table is: = Query_Date(#date(2000, 1, 1), #date(2040, 1, 1), null) ), which is related to mine table by column Date and I have used Date[Date] as axis (Date Hierarchy - Month)  and Date[Year] as Legend. Q-ty is in Values and it worked fine

ex2.jpg

 

Thanks a lot!

Helpful resources

Announcements
October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Badges

Incoming: New and Improved Badges

Exciting news: We've given our badges an overhaul and added brand news ones.

Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 370 members 3,583 guests
Please welcome our newest community members: