Forum Discussion
date
Yes I tried Userelationship, however it is not working, below is the sample dataset. could you please help me to bring total count of created items & completed items in column chart
Work Item Id State Work Item Type Created Date Completed Date Week Created date Week Completion Start 12553 Done Task 23-05-2022 16-06-2022 23-May 13-Jun 12558 Done Task 23-05-2022 07-06-2022 23-May 06-Jun 12559 Done Task 06-06-2022 16-06-2022 06-Jun 13-Jun 12563 Done Bug 04-02-2022 15-06-2022 31-Jan 13-Jun 12564 Done Bug 02-08-2022 05-08-2022 01-Aug 01-Aug 12571 Done Spike 02-06-2021 05-07-2021 31-May 05-Jul 12572 Done Spike 24-06-2021 28-06-2021 21-Jun 28-Jun 12573 Done Spike 03-06-2021 16-06-2021 31-May 14-Jun 12574 Done Product Backlog Item 23-04-2021 15-07-2021 19-Apr 12-Jul 12578 Done Spike 12-07-2021 28-07-2021 12-Jul 26-Jul 12579 Done Product Backlog Item 23-10-2020 19-07-2021 19-Oct 19-Jul 12580 Done Spike 23-06-2021 05-07-2021 21-Jun 05-Jul 12586 Done Spike 14-07-2021 30-07-2021 12-Jul 26-Jul 12602 Done Spike 20-06-2022 30-07-2022 20-Jun 25-Jul 12660 Closed Test Case 01-09-2020 03-09-2020 31-Aug 31-Aug 12661 Closed Test Case 01-09-2020 03-09-2020 31-Aug 31-Aug 12662 Closed Test Case 01-09-2020 03-09-2020 31-Aug 31-Aug 12663 Closed Test Case 01-09-2020 03-09-2020 31-Aug 31-Aug 12664 Closed Test Case 01-09-2020 03-09-2020 31-Aug 31-Aug 12676 Closed Test Case 25-05-2021 24-06-2021 24-May 21-Jun 12680 Closed Test Case 13-07-2021 13-07-2021 12-Jul 12-Jul 12681 Closed Test Case 05-05-2021 09-07-2021 03-May 05-Jul 12685 Closed Test Case 05-05-2021 09-07-2021 03-May 05-Jul 12686 Closed Test Case 25-05-2021 24-06-2021 24-May 21-Jun 12688 Closed Test Case 25-05-2021 24-06-2021 24-May 21-Jun 12689 Closed Test Case 14-07-2021 14-07-2021 12-Jul 12-Jul 12694 Closed Test Case 14-07-2021 14-07-2021 12-Jul 12-Jul 12695 Closed Test Case 25-05-2021 24-06-2021 24-May 21-Jun 12696 Closed Test Case 14-07-2021 14-07-2021 12-Jul 12-Jul 12697 Closed Test Case 02-07-2021 07-07-2021 28-Jun 05-Jul 12698 Closed Test Case 10-06-2021 11-06-2021 07-Jun 07-Jun 12705 Closed Test Case 13-07-2021 04-08-2021 12-Jul 02-Aug 12708 Closed Test Case 14-07-2021 04-08-2021 12-Jul 02-Aug 12709 Closed Test Case 17-08-2021 26-08-2021 16-Aug 23-Aug 12710 Closed Test Case 18-08-2021 27-08-2021 16-Aug 23-Aug 12713 Closed Test Case 02-08-2021 03-08-2021 02-Aug 02-Aug 12714 Closed Test Case 18-08-2021 27-08-2021 16-Aug 23-Aug 12715 Closed Test Case 29-07-2021 30-07-2021 26-Jul 26-Jul 12716 Closed Test Case 27-08-2021 27-08-2021 23-Aug 23-Aug 12718 Closed Test Case 27-08-2021 27-08-2021 23-Aug 23-Aug 12721 Closed Test Case 14-07-2021 04-08-2021 12-Jul 02-Aug 12722 Closed Test Case 17-08-2021 27-08-2021 16-Aug 23-Aug 12746 Closed Test Case 20-06-2022 20-06-2022 20-Jun 20-Jun 12748 Closed Test Case 28-06-2022 28-06-2022 27-Jun 27-Jun 12756 Closed Test Case 21-06-2022 21-06-2022 20-Jun 20-Jun 12761 Closed Test Case 20-07-2022 20-07-2022 18-Jul 18-Jul 12762 Closed Test Case 19-07-2022 19-07-2022 18-Jul 18-Jul 12763 Closed Test Case 27-07-2022 27-07-2022 25-Jul 25-Jul 12773 Done Spike 13-05-2021 01-07-2021 10-May 28-Jun 12775 Done Bug 08-06-2021 16-08-2021 07-Jun 16-Aug 12776 Done Spike 17-06-2021 28-06-2021 14-Jun 28-Jun 12781 Done Bug 08-06-2021 16-08-2021 07-Jun 16-Aug 12783 Done Bug 07-06-2021 16-08-2021 07-Jun 16-Aug 12789 Done Bug 09-06-2021 22-06-2021 07-Jun 21-Jun 12799 Done Bug 16-06-2021 12-07-2022 14-Jun 11-Jul 12800 Done Bug 16-06-2021 05-07-2022 14-Jun 04-Jul 12808 Done Bug 13-01-2022 30-06-2022 10-Jan 27-Jun 12809 Done Bug 23-06-2022 30-06-2022 20-Jun 27-Jun 12810 Done Bug 27-06-2022 30-06-2022 27-Jun 27-Jun 12811 Done Bug 24-06-2022 27-06-2022 20-Jun 27-Jun 12812 Done Bug 31-05-2022 29-06-2022 30-May 27-Jun 12813 Done Bug 23-02-2022 29-06-2022 21-Feb 27-Jun 12815 Done Bug 27-06-2022 01-07-2022 27-Jun 27-Jun
Hi Dane,
Thank you for your reply, the problem here is the completed date is filtered out by created date. For ex: for the weekend starting 25 Jul 2022, the created number is 20(items created between 25th Jul to 31st Jul) similarly the completed items should show 25(Items completed between 25th Jul to 31st Jul). but the problem here is the completed items is considering only the items which got created on the above weekend only, but the requirement here is any items which got completed irrespective of the creation date must show. But unfortunately it is cutting the numbers. so it should not filter the created date . Can you please suggest
Have you tried using USERELATIONSHIP as suggested? The sample is in the pbix I attached. USERELATIONSHIP should allow an inactive relationship to be used in the measure so it doesn't get filtered by the other active relationship using the column/field from another table. If this doesn't work, please post a sample pbix with sanitized data.
Hi,
I am unable to attach my PBIX file, however below is the sample data, where I need to show Total count of Created items & Completed Items in column chart. As per your advise, I have used common date table and Userelationship However it is still not working. Could you please help me to resolve. Very urgent for me. Please. When I use created date in X axis it is cutting the completed items. Also please guide me which date I should use in common slicer to filter the Weekday .
Work Item Id State Work Item Type Created Date Completed Date Week Created date Week Completion Start 12553 Done Task 23-05-2022 16-06-2022 23-May 13-Jun 12558 Done Task 23-05-2022 07-06-2022 23-May 06-Jun 12559 Done Task 06-06-2022 16-06-2022 06-Jun 13-Jun 12563 Done Bug 04-02-2022 15-06-2022 31-Jan 13-Jun 12564 Done Bug 02-08-2022 05-08-2022 01-Aug 01-Aug 12571 Done Spike 02-06-2021 05-07-2021 31-May 05-Jul 12572 Done Spike 24-06-2021 28-06-2021 21-Jun 28-Jun 12573 Done Spike 03-06-2021 16-06-2021 31-May 14-Jun 12574 Done Product Backlog Item 23-04-2021 15-07-2021 19-Apr 12-Jul 12578 Done Spike 12-07-2021 28-07-2021 12-Jul 26-Jul 12579 Done Product Backlog Item 23-10-2020 19-07-2021 19-Oct 19-Jul 12580 Done Spike 23-06-2021 05-07-2021 21-Jun 05-Jul 12586 Done Spike 14-07-2021 30-07-2021 12-Jul 26-Jul 12602 Done Spike 20-06-2022 30-07-2022 20-Jun 25-Jul 12660 Closed Test Case 01-09-2020 03-09-2020 31-Aug 31-Aug 12661 Closed Test Case 01-09-2020 03-09-2020 31-Aug 31-Aug 12662 Closed Test Case 01-09-2020 03-09-2020 31-Aug 31-Aug 12663 Closed Test Case 01-09-2020 03-09-2020 31-Aug 31-Aug 12664 Closed Test Case 01-09-2020 03-09-2020 31-Aug 31-Aug 12676 Closed Test Case 25-05-2021 24-06-2021 24-May 21-Jun 12680 Closed Test Case 13-07-2021 13-07-2021 12-Jul 12-Jul 12681 Closed Test Case 05-05-2021 09-07-2021 03-May 05-Jul 12685 Closed Test Case 05-05-2021 09-07-2021 03-May 05-Jul 12686 Closed Test Case 25-05-2021 24-06-2021 24-May 21-Jun 12688 Closed Test Case 25-05-2021 24-06-2021 24-May 21-Jun 12689 Closed Test Case 14-07-2021 14-07-2021 12-Jul 12-Jul 12694 Closed Test Case 14-07-2021 14-07-2021 12-Jul 12-Jul 12695 Closed Test Case 25-05-2021 24-06-2021 24-May 21-Jun 12696 Closed Test Case 14-07-2021 14-07-2021 12-Jul 12-Jul 12697 Closed Test Case 02-07-2021 07-07-2021 28-Jun 05-Jul 12698 Closed Test Case 10-06-2021 11-06-2021 07-Jun 07-Jun 12705 Closed Test Case 13-07-2021 04-08-2021 12-Jul 02-Aug 12708 Closed Test Case 14-07-2021 04-08-2021 12-Jul 02-Aug 12709 Closed Test Case 17-08-2021 26-08-2021 16-Aug 23-Aug 12710 Closed Test Case 18-08-2021 27-08-2021 16-Aug 23-Aug 12713 Closed Test Case 02-08-2021 03-08-2021 02-Aug 02-Aug 12714 Closed Test Case 18-08-2021 27-08-2021 16-Aug 23-Aug 12715 Closed Test Case 29-07-2021 30-07-2021 26-Jul 26-Jul 12716 Closed Test Case 27-08-2021 27-08-2021 23-Aug 23-Aug 12718 Closed Test Case 27-08-2021 27-08-2021 23-Aug 23-Aug 12721 Closed Test Case 14-07-2021 04-08-2021 12-Jul 02-Aug 12722 Closed Test Case 17-08-2021 27-08-2021 16-Aug 23-Aug 12746 Closed Test Case 20-06-2022 20-06-2022 20-Jun 20-Jun 12748 Closed Test Case 28-06-2022 28-06-2022 27-Jun 27-Jun 12756 Closed Test Case 21-06-2022 21-06-2022 20-Jun 20-Jun 12761 Closed Test Case 20-07-2022 20-07-2022 18-Jul 18-Jul 12762 Closed Test Case 19-07-2022 19-07-2022 18-Jul 18-Jul 12763 Closed Test Case 27-07-2022 27-07-2022 25-Jul 25-Jul 12773 Done Spike 13-05-2021 01-07-2021 10-May 28-Jun 12775 Done Bug 08-06-2021 16-08-2021 07-Jun 16-Aug 12776 Done Spike 17-06-2021 28-06-2021 14-Jun 28-Jun 12781 Done Bug 08-06-2021 16-08-2021 07-Jun 16-Aug 12783 Done Bug 07-06-2021 16-08-2021 07-Jun 16-Aug 12789 Done Bug 09-06-2021 22-06-2021 07-Jun 21-Jun 12799 Done Bug 16-06-2021 12-07-2022 14-Jun 11-Jul 12800 Done Bug 16-06-2021 05-07-2022 14-Jun 04-Jul 12808 Done Bug 13-01-2022 30-06-2022 10-Jan 27-Jun 12809 Done Bug 23-06-2022 30-06-2022 20-Jun 27-Jun 12810 Done Bug 27-06-2022 30-06-2022 27-Jun 27-Jun 12811 Done Bug 24-06-2022 27-06-2022 20-Jun 27-Jun 12812 Done Bug 31-05-2022 29-06-2022 30-May 27-Jun 12813 Done Bug 23-02-2022 29-06-2022 21-Feb 27-Jun 12815 Done Bug 27-06-2022 01-07-2022 27-Jun 27-Jun