Forum Discussion
Plotting 2 dates from same data table on a line and stacked column chart
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.
Microsoft Employee
| Project number | Opened date | Closed date | Priority |
| 6775 | 1/06/2017 | 30/06/2017 | 3 |
| 6776 | 1/06/2017 | 30/06/2017 | 3 |
| 6777 | 1/06/2017 | 1/11/2017 | 3 |
| 6778 | 1/06/2017 | 30/10/2017 | 3 |
| 6779 | 1/07/2017 | 30/07/2017 | 1 |
| 6780 | 1/07/2017 | 2 | |
| 6781 | 1/07/2017 | 1/11/2017 | 5 |
| 6782 | 1/07/2017 | 30/10/2017 | 4 |
| 6783 | 1/07/2017 | 30/07/2017 | 4 |
| 6784 | 1/08/2017 | 15/11/2017 | 5 |
| 6785 | 1/08/2017 | 31/08/2017 | 3 |
| 6786 | 1/08/2017 | 1/09/2017 | 3 |
| 6787 | 1/08/2017 | 1/09/2017 | 3 |
| 6788 | 1/08/2017 | 1/09/2017 | 3 |
| 6789 | 1/09/2017 | 1/10/2017 | 3 |
| 6790 | 1/09/2017 | 1/10/2017 | 3 |
| 6791 | 1/09/2017 | 15/09/2017 | 2 |
| 6792 | 1/10/2017 | 30/10/2017 | 1 |
| 6793 | 1/10/2017 | 30/10/2017 | 4 |
| 6794 | 1/10/2017 | 30/10/2017 | 4 |
| 6795 | 1/10/2017 | 15/11/2017 | 4 |
| 6796 | 1/11/2017 | 5 | |
| 6797 | 1/11/2017 | 1 | |
| 6798 | 1/11/2017 | 15/11/2017 | 2 |
| 6799 | 1/11/2017 | 15/11/2017 | 3 |
| 6800 | 1/11/2017 | 15/11/2017 | 3 |
| 6801 | 1/11/2017 | 5/12/2017 | 3 |
| 6802 | 1/11/2017 | 5/12/2017 | 3 |
| 6803 | 1/12/2017 | 3 | |
| 6804 | 1/12/2017 | 3 |
- Phil_Seamark
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.
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
- Phil_Seamark
Are the hours/minutes important? Otherwise convert the column to be using DATE instead of DateTime.
AkshayMankeHelper II
Thanks a lot for this solution. I was looking for it from a long time.
Hi Phil,
I used your formula to create a Date table
Dates = ADDCOLUMNS(CALENDARAUTO() ,"MonthID" , INT(FORMAT([Date],"YYYYMM")),"Month" , FORMAT([Date],"MMM YY"))However, it gives me a huge range of date. From 2006 - 2100. May I know how do I limit the time period to just 2019 - 2022?