Forum Discussion
Plotting multiple dates per item in a scatter plot
It seems like the best way to handle this is going to be to unpivot your data. Transforming it from 1 row per project to multiple rows per project will allow you to plot this the way you want. I went from this:
Project Initial Release Actual Release Release in US Release in EU ABC 1/1/2019 2/1/2019 3/1/2019 4/1/2019 DEF 1/1/2019 1/1/2019 1/15/2019 3/15/2019 GHI 1/31/2019 3/2/2019 2/2/2019 To this:
Project Release Type Date ABC Initial Release 1/1/2019 ABC Actual Release 2/1/2019 ABC Release in US 3/1/2019 ABC Release in EU 4/1/2019 DEF Initial Release 1/1/2019 DEF Actual Release 1/1/2019 DEF Release in US 1/15/2019 DEF Release in EU 3/15/2019 GHI Initial Release 1/31/2019 GHI Release in US 3/2/2019 GHI Release in EU 2/2/2019 This puts all dates in the same column, while still associating them with a release type and project.
I ended up using the Dot Plot by MAQ Software, since it gives slightly more control over the fields and allows you to jitter the plot points, so that you don't accidentally hide data. The first thing I did was create a numerical project ID, since all the scatter plots don't like categorical Y axes.
ProjectID = RANKX(ALLSELECTED('Project Releases (2)'), 'Project Releases (2)'[Project], SELECTEDVALUE('Project Releases (2)'[Project]), ASC, Dense)The actual ID doesn't matter, just the fact that each project gets a unique numerical number.
From there, I played around with the various options until I got a scatter plot that looks vaguely like the one you described:
Honestly, at the end of the day, this is not a particularly elegant solution. The dates are categorical, you can't color by Project name, there's no easy way to see what the project name is without hovering over a point, among others. The current available scatter plot options just do not play well with data that isn't straight numerical info with a single category.
You're more likely to be able to find success by using one of the calendar or timeline visualizations out there instead of trying to force a scatterplot to do this work for you. Good luck
A mapping table would likely work.
As far as actually using categorical data as an axis, there might be some way with the MAQ dot plot to swap the axes, but it still doesn't treat dates as continuous as far as I can tell.
The other option is to develop your own dot plot visual, but that may be a little bit too much effort for this.
It seems like the best way to handle this is going to be to unpivot your data. Transforming it from 1 row per project to multiple rows per project will allow you to plot this the way you want. I went from this:
| Project | Initial Release | Actual Release | Release in US | Release in EU |
| ABC | 1/1/2019 | 2/1/2019 | 3/1/2019 | 4/1/2019 |
| DEF | 1/1/2019 | 1/1/2019 | 1/15/2019 | 3/15/2019 |
| GHI | 1/31/2019 | 3/2/2019 | 2/2/2019 |
To this:
| Project | Release Type | Date |
| ABC | Initial Release | 1/1/2019 |
| ABC | Actual Release | 2/1/2019 |
| ABC | Release in US | 3/1/2019 |
| ABC | Release in EU | 4/1/2019 |
| DEF | Initial Release | 1/1/2019 |
| DEF | Actual Release | 1/1/2019 |
| DEF | Release in US | 1/15/2019 |
| DEF | Release in EU | 3/15/2019 |
| GHI | Initial Release | 1/31/2019 |
| GHI | Release in US | 3/2/2019 |
| GHI | Release in EU | 2/2/2019 |
This puts all dates in the same column, while still associating them with a release type and project.
I ended up using the Dot Plot by MAQ Software, since it gives slightly more control over the fields and allows you to jitter the plot points, so that you don't accidentally hide data. The first thing I did was create a numerical project ID, since all the scatter plots don't like categorical Y axes.
ProjectID = RANKX(ALLSELECTED('Project Releases (2)'), 'Project Releases (2)'[Project], SELECTEDVALUE('Project Releases (2)'[Project]), ASC, Dense)The actual ID doesn't matter, just the fact that each project gets a unique numerical number.
From there, I played around with the various options until I got a scatter plot that looks vaguely like the one you described:
Honestly, at the end of the day, this is not a particularly elegant solution. The dates are categorical, you can't color by Project name, there's no easy way to see what the project name is without hovering over a point, among others. The current available scatter plot options just do not play well with data that isn't straight numerical info with a single category.
You're more likely to be able to find success by using one of the calendar or timeline visualizations out there instead of trying to force a scatterplot to do this work for you. Good luck
Cmcmahan This is great, thank you so much! I agree that using a calendar/timeline is much easier and I do have those visuals already on my dashboard, but I was hoping to find different ways to visualize the data. Is there any possibility of a work around to get non-numerical data (the Project name) displayed on either of the axes?
The only other alternative I can think of is to display a mapping table (projectID to Project name) or to just hover over a point to read the Project name.
A mapping table would likely work.
As far as actually using categorical data as an axis, there might be some way with the MAQ dot plot to swap the axes, but it still doesn't treat dates as continuous as far as I can tell.
The other option is to develop your own dot plot visual, but that may be a little bit too much effort for this.
Cmcmahan Thanks for the help!