Comparing years in line graph

[josephmwood]

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.

[FrankAT]

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)

[KNP]

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.

[josephmwood]

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

[KNP]

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

[josephmwood]

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!

[KNP]

I understand. To use my sample code follow the below steps.

Go to Transform data.

Right click in a blank area in the query pane and select New Query --> Blank Query

With the query selected, choose Advanced Editor

Paste my code over what is there and click done.

You'll then be able to see the steps I followed.

Essentially, all you're trying to do is add a column for year for the legend and one for a date part of some kind that excludes year (I used week number) for the X axis.

What you end up with is...

[josephmwood]

Thanks so much! 

I did this on my table and it created the new queries and i see them, but they have no effect on my table right now. I tried creating relationships between the date and my tables, but it doesn't do anything. Do I have to customize your code to fit with my tables and correct column names that I'm pulling from my server? I'm using several different sections of a server to bring information together.

[KNP]

I only included that query to serve as an example of how I'd approach it so you could see the individual steps. Sorry, I may not have explained that well. 

What you need is a 'year' (in your date dimension if you have one) for the legend and something for your X axis like week number. 

Are you able to attach a screen shot of your model showing the joins? I might be able to offer better advice if I can see the data model.

[josephmwood]

I appreciate you helping me out!

Here's a screenshot of the settings for my current line graph. 

Let me know if that helps at all.

[KNP]

It's the screenshot of the model that would be most helpful. (and probably the graph)

How does the Power BI version of the graph look right now?

[KNP]

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#M6071...)

Sounds super easy. I'll be happy to help with some more info.