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

Current year, last year, and 2 year ago line chart

This seems like it should be incredibly easy but after hours of looking things up and trial and error, I am stumped. 

 

I know people have asked for sample data but not sure how to post without revealing sensitive data. Please let me know how I can do that.

 

I have a "Volume" column with a specific close date, a date table linked to that close date with a full "date" and a "year" column. 

 

I want to show by month (on the line graph) current year volume, last years volume (2016), and 2 years ago volume (2015). The idea is to show our managers our associate trends based on the past 3 years. I am assuming this would be 3 measures but I can't figure out the right formula for any of them. Let me know what else you need to know to point me in the right direction. 

 

Many Thanks!

3 REPLIES 3
Highlighted
Community Champion
Community Champion

Re: Current year, last year, and 2 year ago line chart

@JasonP

 

If you have a calendar Table you can use:

 

Measure1=Calculate(Sum[Volumn])

Measure1yearago=Calculate(Sum[Volumn]),DateAdd(Calendar[date],-1,Year))

Measure2yearsago=Calculate(Sum[Volumn]),DateAdd(Calendar[date],-2,Year))




Lima - Peru
Highlighted
Advocate III
Advocate III

Re: Current year, last year, and 2 year ago line chart

I would add there could be additional complication if you are using a filter on the year for your data. What I mean is if you want to be able to select 2017 and look at -1 (2016) and -2 (2015), or select  2016 and look at -1 (2015) and -2 (2014) then you need a little extra work. I described the problem and my journey to the solution in this thread.

 

https://community.powerbi.com/t5/Desktop/SAMEPERIODLASTYEAR-with-a-year-filter/td-p/91501

 

Or make it easy on yourself and don't use a filter, always using current date only. 🙂

 

 

Highlighted
Community Champion
Community Champion

Re: Current year, last year, and 2 year ago line chart

@JasonPYou can actually do this with only Measure - just set up as in the picture

Month on Axis

Year on Legend (and possibly in a slicer - so you can select as many or as few as you want)

1 Measure in Values => Volume Measure = SUM ( Table[Volume Column] )

Of course the measure can have filters if you are only summing volume of certain type for example...

 

Volume per Year.png

 

 

 

That's it! Smiley Happy

Good Luck!

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Upcoming Events

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors