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.
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.
Microsoft EmployeeIt 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.