Hey PBI Experts,
I would like to show a bar and line graph which has these 3 components: Closed tasks (as a bar), open tasks (as a bar), backlog (as a line), the x axis is year-month (a calculated column).
Where open tasks are the tasks that were opened in this specific month or which were opened but still not closed in this specific month and the backlog is the difference of tickets that were closed this month- the tasks that were opened or are still open during this month.
I am having trouble in calculating the open tasks and backlog and would really appreciate if anyone can help with this. my dataset has a 'task number', 'opened date' and 'closed date'. I have included my dataset sample here:
Number Opened Date Closed Date
TSK01145209 15/05/2020 12:15 09/06/2020 10:01
TSK01245636 26/05/2020 23:03 02/07/2020 10:01
TSK00976516 27/04/2020 11:47 06/05/2020 13:01
TSK02226055 09/09/2020 09:30 30/09/2020 09:02
TSK01168103 18/05/2020 14:12 08/06/2020 08:00
TSK01366880 09/06/2020 23:58 24/06/2020 22:43
TSK01250660 27/05/2020 12:21 12/06/2020 11:00
TSK02597832 19/10/2020 08:54 27/10/2020 12:01
TSK02404259 28/09/2020 05:26 04/11/2020 15:02
TSK02507457 09/10/2020 08:31 28/10/2020 07:00
TSK01233273 25/05/2020 16:31 08/07/2020 15:01
TSK00986657 28/04/2020 11:02 18/05/2020 08:00
TSK00865924 15/04/2020 16:17 05/06/2020 11:02
TSK01164533 18/05/2020 07:46 01/06/2020 10:01
TSK01333693 05/06/2020 19:19 23/06/2020 13:02
TSK02378499 24/09/2020 13:20 02/10/2020 14:00
TSK01562659 01/07/2020 06:09 13/07/2020 10:00
TSK01687737 14/07/2020 14:30 24/07/2020 14:00
TSK00972303 27/04/2020 03:35 11/05/2020 09:03
TSK02053434 21/08/2020 16:10 07/09/2020 11:00
TSK02018276 18/08/2020 04:27 01/09/2020 06:00
TSK01477480 22/06/2020 09:09 30/06/2020 13:37
TSK01515027 25/06/2020 21:45 07/07/2020 16:01
TSK02419455 29/09/2020 14:24 12/10/2020 11:01
TSK01421001 16/06/2020 11:47 25/06/2020 18:00
TSK01300636 02/06/2020 12:57 01/07/2020 08:03
TSK00338276 24/02/2020 02:31 06/03/2020 02:00
TSK01820517 28/07/2020 09:06 05/08/2020 16:00
TSK01360940 09/06/2020 09:46 17/06/2020 12:02
TSK01206317 22/05/2020 09:29 23/06/2020 13:01
TSK01381560 11/06/2020 15:30 25/06/2020 12:00
TSK02541329 13/10/2020 13:12 26/10/2020 12:00
TSK02607403 20/10/2020 05:47 28/10/2020 08:00
TSK02318016 18/09/2020 11:00 20/10/2020 14:03
TSK02505889 09/10/2020 05:11 26/10/2020 04:00
TSK01286281 01/06/2020 05:01 10/06/2020 07:01
TSK01893560 05/08/2020 08:25 12/08/2020 09:00
TSK01078476 08/05/2020 02:55 25/05/2020 07:01
TSK01511949 25/06/2020 14:44 04/08/2020 16:01
TSK00844359 13/04/2020 13:04 25/04/2020 10:01
TSK02516583 10/10/2020 07:34 21/10/2020 10:01
TSK00808906 08/04/2020 11:05 21/04/2020 06:00
TSK01183675 20/05/2020 03:29 29/05/2020 11:00
TSK00889827 18/04/2020 00:25 29/04/2020 13:02
TSK00816803 09/04/2020 07:53 23/04/2020 09:00
TSK01047170 04/05/2020 22:33 26/05/2020 16:01
TSK00855634 14/04/2020 16:05 08/05/2020 14:01
TSK02139613 31/08/2020 11:25 09/09/2020 08:01
TSK00342474 24/02/2020 10:34 19/03/2020 12:00
TSK00822845 09/04/2020 22:22 21/04/2020 22:00
TSK00976585 27/04/2020 11:56 05/05/2020 12:00
TSK00438011 04/03/2020 13:53 04/05/2020 08:00
TSK01098382 10/05/2020 14:28 12/08/2020 09:01
TSK01135906 14/05/2020 12:53 12/08/2020 09:01
TSK00486939 09/03/2020 10:25 23/03/2020 15:02
TSK02304276 17/09/2020 04:36 25/09/2020 05:00
TSK01690992 14/07/2020 21:38 24/07/2020 16:01
TSK02040578 20/08/2020 08:46 31/08/2020 13:01
TSK00812558 08/04/2020 19:11 21/04/2020 22:00
TSK01167252 18/05/2020 12:48 30/05/2020 11:00
TSK02596655 19/10/2020 06:50 03/11/2020 03:00
TSK02155820 01/09/2020 21:41 21/09/2020 15:02
TSK02674165 26/10/2020 17:26 06/11/2020 17:02
TSK02043519 20/08/2020 14:38 01/09/2020 14:01
TSK01669863 12/07/2020 14:42 09/08/2020 07:00
TSK01909560 06/08/2020 15:42 19/08/2020 05:00
TSK01922755 07/08/2020 18:48 21/08/2020 15:00
TSK00921712 21/04/2020 13:21 05/05/2020 15:01
TSK00600960 18/03/2020 17:22 26/03/2020 18:00
TSK01893851 05/08/2020 08:59 18/08/2020 16:00
TSK00815693 09/04/2020 05:10 22/04/2020 12:00
TSK01033283 03/05/2020 11:22 12/05/2020 18:01
TSK02512742 09/10/2020 20:05 28/10/2020 05:00
TSK01110841 12/05/2020 00:28 23/05/2020 12:00
TSK01169922 18/05/2020 17:51 30/05/2020 11:00
TSK02208095 07/09/2020 13:53 15/09/2020 15:00
TSK01053537 05/05/2020 13:05 28/05/2020 09:00
TSK00384454 28/02/2020 14:48 24/03/2020 13:00
TSK02224119 09/09/2020 06:00 25/09/2020 13:00
TSK01954263 11/08/2020 10:47 19/08/2020 11:01
TSK01823672 28/07/2020 14:38 05/08/2020 15:01
TSK02164812 02/09/2020 18:50 30/10/2020 23:00
TSK02618176 21/10/2020 06:29 03/11/2020 19:00
TSK01965607 12/08/2020 10:48 15/09/2020 16:00
TSK02623230 21/10/2020 15:12
TSK02666839 26/10/2020 06:57
TSK02688579 27/10/2020 12:48
TSK02783155 04/11/2020 08:46
