Forum Discussion
Duplicate PivotChart Functionality
It is impossible to give "exact steps" without sample data.
Can you provide a screenshot of your underlying data structure? Below is the structure you will need to create to get three lines in a chart that represent individual calendar years:
Month+Date Key | 2012 data | 2013 data | 2014 data | Date Axis Sort Order
Jan 1 x x x 1
Jan 2 x x x 2
Jan 3 x x x 3
...
etc.
Then you would create a line chart and put the Month+Date Key as the axis. Sort the Month+Date Key field by the Date Axis Sort Order column (under the Modeling tab I believe). Then add the 2012, 2013, and 2014 fields to your Values area and they will share the common axis but each represent a separate year.
If you want to only display 1 year at a time and have a slicer for year, then the table structure needs to be different, or you have to do some more complex DAX which is probably not a good choice given your challenges so far.
Hi,
Take a look at these samples I made. Download both files and place them in the same folder. Open the pbix file in Power BI Desktop. Click on the categories in the charts to see what the brushing does. (like cross filtering.)
Regards,
Garry
Thanks Garry,
That looks like it will work quite well for what the user wants, except that I still don't see a way to compare, let's say, three years of data on a line chart. He would like to see the 2014 year compared to 2015 and 2016. Next level in would be monthly comparison, weekly comparison, etc. There are many years of data in one dataset so breaking thsi dataset into individual years is where I am coming up short.
Since this was typed but never posted, I have a comment to post for review/suggestion. It sounds like I can't divide a single dataset into multiples by year within PowerBI. If I create three queries by year with Access and use those three queries as the data sources, will that show three lines that I can compare as explained above.
Are you saying you want three lines on the same chart with month as the X axis, with each line for each of the years? You could very easily put month name on the axis and your data as the values and then add a slicer for Year, but that will only show one year at a time. Otherwise, you will have to do some more "advanced" ETL to get the data in a format where your have different fields for different years.
Comparing three years of data on one line chart would seem to be a basic expectation of PowerBI since it is an attempt to replace the Pivot Chart that was removed from Access. Why this is so hard to accomplish is beyond me. I have created three queries with a year filter in Access. Then I placed the data from all three queries on a line chart. I see three lines, but only one year is broken out. The other two years display a solid line with the total for the year.
The "difficulty" is a function of your data structure and data model, not the tool. To have three separate lines, you need three separate fields in your table. So you would have column 1 is a day of year or Month+Day key, and then column 2 would be 2014 values, column 3 would be 2015 values, etc for those days. If your values data is in one continuous field and not broken out by year, you will need to do that.
I will take a look at the exact steps later but you should be able to do this easily if you first add a calculated column (in query editor) to extract year, transform the date to only be month, week, or day of year etc, then perform a pivot to get distinct years as column headers with subsequent values for each month, week or day of year.
I am still quite baffled. It finally made sense that I should be able to create three queries, in this case for the 2012/2013/2014 data. I should then create expressions with Month([Date]) and Day([Date]). Then I thought I could add these three queries as data sources and place the three fields (either day or month) on the line chart, but that didn't work either. Creating calculated columns on an existing query seems convoluted ... awaiting exact steps.
It is impossible to give "exact steps" without sample data.
Can you provide a screenshot of your underlying data structure? Below is the structure you will need to create to get three lines in a chart that represent individual calendar years:
Month+Date Key | 2012 data | 2013 data | 2014 data | Date Axis Sort Order
Jan 1 x x x 1
Jan 2 x x x 2
Jan 3 x x x 3
...
etc.
Then you would create a line chart and put the Month+Date Key as the axis. Sort the Month+Date Key field by the Date Axis Sort Order column (under the Modeling tab I believe). Then add the 2012, 2013, and 2014 fields to your Values area and they will share the common axis but each represent a separate year.
If you want to only display 1 year at a time and have a slicer for year, then the table structure needs to be different, or you have to do some more complex DAX which is probably not a good choice given your challenges so far.