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
JasonP
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
Sean
Community Champion
Community Champion

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

Vvelarde
Community Champion
Community Champion

@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

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. 🙂

 

 

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.