Forum Discussion
Comparing years in line graph
I saw a previous message that was similar to this, but I could not figure out how to get the answer to work at all.
I have a dashboard I have made in Excel using a pivot chart from a pivot table. It tracks attendance at a weekly event for the last few years. I have slicers set up to allow people to compare year over year. It's so simple to just click a year and it overlays right on top of the current year.
I can't figure out how to do that in PowerBI. I've seen at least 5 different people all give different solutions on different forums, but I can't get any of them to work for me.
11 Replies
Hi josephmwood - some screenshots of desired outcome and some sample data will help get this answered much quicker. (read post by Greg_Deckler: https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523#M607150)
Sounds super easy. I'll be happy to help with some more info.
Here's what my dashboard looks like in excel. I'll upload a spreadsheet with most of the data that I'm wanting to use. I'm actually connecting to a SQL Server for PowerBI, but the data is the same (I'm looking at moving this dashbaord to a PowerBI site that people can check on their phone).
Let me know if this is enough data.Date Venue Type Service Venue Total Sunday, May 16, 2021 Combined Total All Services All Venues Total 1336 Sunday, May 9, 2021 Combined Total All Services All Venues Total 1381 Sunday, May 17, 2020 Combined Total All Services All Venues Total 90 Sunday, May 10, 2020 Combined Total All Services All Venues Total 104 Sunday, May 19, 2019 Combined Total All Services All Venues Total 2583 Sunday, May 12, 2019 Combined Total All Services All Venues Total 2653 Thanks for that.
With a couple of steps, that data, looks like this...
The important part, add a new column for 'year' (for your legend), and add a new column for your X axis, I did week of year but you can adapt to your needs.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCi7NS0ms1FHwTaxUMDTTUTAyMDJU0lFyzs9NysxLTVEIyS9JzAEKOObkKASnFpVlJqcWQ7lhqXmlqcVwFYbGxmZKsTqoRlpSZqKFIYaJhuZgIw3IM9LSANNAA0oMNDQwwTQR7GtDS/JMNDK1MMY00ogiI81MgUbGAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, #"Venue Type" = _t, Service = _t, Venue = _t, Total = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Venue Type", type text}, {"Service", type text}, {"Venue", type text}, {"Total", Int64.Type}}), #"Inserted Year" = Table.AddColumn(#"Changed Type", "Year", each Date.Year([Date]), Int64.Type), #"Inserted Week of Year" = Table.AddColumn(#"Inserted Year", "Week of Year", each Date.WeekOfYear([Date]), Int64.Type) in #"Inserted Week of Year"Hope this helps.
Regards,
Kim
I appreciate the quick response Kim!
Unfortunately I'm a complete noob to PowerBI and that code makes zero sense to me and I would have absolutely no idea how to implement it in my PowerBI dashboard that I have connected to my SQL server. Is there anyway you could explain it a bit clearer to me? I'm not in a hurry at all. Thanks so much!
Hi josephmwood ,
take a look at the attached PBIX file.
With kind regards from the town where the legend of the 'Pied Piper of Hamelin' is at home
FrankAT (Proud to be a Datanaut)DAX measures are an option I guess but it should be noted that this is not a very dynamic solution as a new measure would need to be added every year.
Also, I think the X axis having year included is misleading in this scenario.
