Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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!
@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...
That's it!
Good Luck!
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))
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. 🙂
User | Count |
---|---|
126 | |
108 | |
99 | |
65 | |
62 |
User | Count |
---|---|
136 | |
113 | |
102 | |
71 | |
60 |