Forum Discussion

SEliza86's avatar
SEliza86
Frequent Visitor
8 years ago
Solved

Plotting 2 dates from same data table on a line and stacked column chart

    How do I plot this on a line and stacked column chart, so the closed date is accurately represented? When I create it with the open date as the 'shared axis', the prioirty as the 'column...
  • Phil_Seamark's avatar
    Phil_Seamark
    8 years ago

    Hi SEliza86

     

    I have created a PBIX file using your data here

     

    https://1drv.ms/u/s!AtDlC2rep7a-oDq7OZESviMpetzu

     

    Basically it uses the data you provided in a table.  I then create two relationships to a separate date table.  One relationship handles the Opened Date (the default active relationship), while the 2nd relationship handles the count of the Closed date

     

    I use a Month column from the Date table in my axis and plot as per my PBIX file.  I hope it makes sense.

Phil_Seamark's avatar
Phil_Seamark
Icon for Microsoft Employee rankMicrosoft Employee
8 years ago

Hi SEliza86

 

Any chance you can paste your data in as text to save typing it all out by hand?

  • SEliza86's avatar
    SEliza86
    Frequent Visitor
    8 years ago
    Project numberOpened dateClosed datePriority
    67751/06/201730/06/20173
    67761/06/201730/06/20173
    67771/06/20171/11/20173
    67781/06/201730/10/20173
    67791/07/201730/07/20171
    67801/07/2017 2
    67811/07/20171/11/20175
    67821/07/201730/10/20174
    67831/07/201730/07/20174
    67841/08/201715/11/20175
    67851/08/201731/08/20173
    67861/08/20171/09/20173
    67871/08/20171/09/20173
    67881/08/20171/09/20173
    67891/09/20171/10/20173
    67901/09/20171/10/20173
    67911/09/201715/09/20172
    67921/10/201730/10/20171
    67931/10/201730/10/20174
    67941/10/201730/10/20174
    67951/10/201715/11/20174
    67961/11/2017 5
    67971/11/2017 1
    67981/11/201715/11/20172
    67991/11/201715/11/20173
    68001/11/201715/11/20173
    68011/11/20175/12/20173
    68021/11/20175/12/20173
    68031/12/2017 3
    68041/12/2017 3
    • Phil_Seamark's avatar
      Phil_Seamark
      Icon for Microsoft Employee rankMicrosoft Employee
      8 years ago

      Hi SEliza86

       

      I have created a PBIX file using your data here

       

      https://1drv.ms/u/s!AtDlC2rep7a-oDq7OZESviMpetzu

       

      Basically it uses the data you provided in a table.  I then create two relationships to a separate date table.  One relationship handles the Opened Date (the default active relationship), while the 2nd relationship handles the count of the Closed date

       

      I use a Month column from the Date table in my axis and plot as per my PBIX file.  I hope it makes sense.

      • SEliza86's avatar
        SEliza86
        Frequent Visitor
        8 years ago

        That's awesome, thanks!

         

        However, when I apply this logic to a different but similar data set I dont get the historic monthly view, just one big column:

        The only difference I can see is the open date in my new data set are second specific. Here's an example of the dates in my new data set (could the relationships in the table no longer be working cause the new open dates are so random?):

        Open Date
        3-Dec-2017 12:35:44 PM
        3-Dec-2017 5:03:25 AM
        29-Nov-2017 10:48:19 AM
        28-Nov-2017 11:14:53 AM
        28-Nov-2017 9:29:32 AM
        27-Nov-2017 10:02:21 AM
        22-Nov-2017 5:00:21 PM
        22-Nov-2017 10:49:02 AM
        21-Nov-2017 4:39:51 PM
        20-Nov-2017 12:35:07 PM
        15-Nov-2017 2:45:40 PM
        13-Nov-2017 4:17:59 PM
        13-Nov-2017 3:23:45 PM
        13-Nov-2017 12:25:54 PM
        10-Nov-2017 2:18:24 PM
        10-Nov-2017 1:48:33 PM
        10-Nov-2017 11:31:30 AM
        10-Nov-2017 10:42:30 AM
        10-Nov-2017 10:40:20 AM
        9-Nov-2017 2:43:12 PM
        9-Nov-2017 10:18:25 AM

         

         Thanks