Forum Discussion

josephmwood's avatar
josephmwood
Regular Visitor
5 years ago

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....
Need Help
josephmwood's avatar
josephmwood
Regular Visitor
5 years ago

 

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.

 

DateVenue TypeServiceVenueTotal
Sunday, May 16, 2021Combined TotalAll ServicesAll Venues Total1336
Sunday, May 9, 2021Combined TotalAll ServicesAll Venues Total1381
Sunday, May 17, 2020Combined TotalAll ServicesAll Venues Total90
Sunday, May 10, 2020Combined TotalAll ServicesAll Venues Total104
Sunday, May 19, 2019Combined TotalAll ServicesAll Venues Total2583
Sunday, May 12, 2019Combined TotalAll ServicesAll Venues Total2653
KNP's avatar
KNP
Super User
5 years ago

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's avatar
    josephmwood
    Regular Visitor
    5 years ago

    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's avatar
      KNP
      Super User
      5 years ago

      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's avatar
        josephmwood
        Regular Visitor
        5 years ago

        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.