Forum Discussion
Urgent Date interval same column
Hello, guys!
I need help to calculate a range of days for combining some of these statuses.
As an example:
-ID 8944: The interval between the "new" and "prioritized" must be 6 days;
-ID 8964: The interval between the "new" and "prioritized" must be 3 days;
-ID 8968: The interval between the "new" and "prioritized" must be 2 days;
In addition, I must also have the media by work item type:
Feature: (6+3)/2 = 4,5 days
Product Backlog = 2 days
| Work Item Id | Work Item Type | STATE DATE | State |
| 8944 | Feature | 01/11/2019 | Prioritized |
| 8944 | Feature | 25/10/2019 | New |
| 8944 | Feature | 30/10/2019 | Approved |
| 8964 | Feature | 12/11/2019 | In Progress |
| 8964 | Feature | 01/11/2019 | Prioritized |
| 8964 | Feature | 28/10/2019 | New |
| 8964 | Feature | 30/10/2019 | Approved |
| 8968 | Product Backlog | 29/10/2019 | New |
| 8968 | Product Backlog | 30/10/2019 | Approved |
| 8968 | Product Backlog | 01/11/2019 | Prioritized |
| 8968 | Product Backlog | 12/11/2019 | In Progress |
Anonymous
If you change the measure to remove the -1 then the datediff will return blank for those with no new or prioritized date but then the number of days don't match what you had in your first post but they do match what you had in your second post. The -1 was just there to force the calc to match your first post.
Date Diff = AVERAGEX( VALUES('MEDIAS STATUS'[Work Item Id]), (DATEDIFF( CALCULATE( MAX ( 'MEDIAS STATUS'[STATE DATE] ), ALLEXCEPT('MEDIAS STATUS','MEDIAS STATUS'[Work Item Id]),'MEDIAS STATUS'[State] = "New"), CALCULATE( MAX ( 'MEDIAS STATUS'[STATE DATE] ), ALLEXCEPT('MEDIAS STATUS','MEDIAS STATUS'[Work Item Id]),'MEDIAS STATUS'[State] = "Prioritized"), DAY ) ) )
3 Replies
Hello Anonymous
I believe this will give you what you are looking for.
Date Diff = AVERAGEX( VALUES(YourTable[Work Item Id]), (DATEDIFF( CALCULATE( MAX ( YourTable[STATE DATE] ), ALLEXCEPT(YourTable,YourTable[Work Item Id]),YourTable[State] = "New"), CALCULATE( MAX ( YourTable[STATE DATE] ), ALLEXCEPT(YourTable,YourTable[Work Item Id]),YourTable[State] = "Prioritized"), DAY )-1) )If this solves your issues please mark it as the solution. Kudos 👍 are nice too.
First of all, thank you so much for trying to help me!
I noticed that in your solution, if there is no status (new or prioritized), the value "-1" is returned, but I would like it not to be calculated (could return blank).
Also, I noticed that some IDs average are going wrong (8744 must be 2 days instead 1,8737 must be 3 instead 2 days).It seems like it's always returning one day less.
MEASURE =
AVERAGEX(
VALUES('MEDIAS STATUS'[Work Item Id]);
(DATEDIFF(
CALCULATE( MAX ( 'MEDIAS STATUS'[STATE DATE] ); ALLEXCEPT('MEDIAS STATUS';'MEDIAS STATUS'[Work Item Id]);'MEDIAS STATUS'[State] = "New");
CALCULATE( MAX ( 'MEDIAS STATUS'[STATE DATE] ); ALLEXCEPT('MEDIAS STATUS';'MEDIAS STATUS'[Work Item Id]);'MEDIAS STATUS'[State] = "Prioritized");
DAY )-1) )Work Item Id Work Item Type State STATE DATE Proposed Total 8751 Feature Approved 10/10/2019 00:00 -1 8751 Feature Prioritized 11/10/2019 00:00 -1 8751 Feature In Progress 16/10/2019 00:00 -1 8751 Feature Done 30/10/2019 00:00 -1 8744 Feature New 09/10/2019 00:00 1 8744 Feature Approved 10/10/2019 00:00 1 8744 Feature Prioritized 11/10/2019 00:00 1 8744 Feature In Progress 25/10/2019 00:00 1 8744 Feature Done 30/10/2019 00:00 1 8742 Feature New 09/10/2019 00:00 1 8742 Feature Approved 10/10/2019 00:00 1 8742 Feature Prioritized 11/10/2019 00:00 1 8742 Feature In Progress 14/10/2019 00:00 1 8742 Feature Done 30/10/2019 00:00 1 8737 Feature New 08/10/2019 00:00 2 8737 Feature Approved 10/10/2019 00:00 2 8737 Feature Prioritized 11/10/2019 00:00 2 8737 Feature In Progress 16/10/2019 00:00 2 8737 Feature Done 30/10/2019 00:00 2 8725 Feature New 05/10/2019 00:00 5 8725 Feature Approved 10/10/2019 00:00 5 8725 Feature Prioritized 11/10/2019 00:00 5 8725 Feature In Progress 14/10/2019 00:00 5 8725 Feature Done 29/10/2019 00:00 5 8359 Feature New 04/09/2019 00:00 36 8359 Feature Approved 10/10/2019 00:00 36 8359 Feature Prioritized 11/10/2019 00:00 36 8359 Feature In Progress 14/10/2019 00:00 36 8359 Feature Done 30/10/2019 00:00 36 8285 Feature New 23/08/2019 00:00 -1 8285 Feature Approved 30/08/2019 00:00 -1 8285 Feature In Progress 08/09/2019 00:00 -1 8285 Feature Done 10/10/2019 00:00 -1 8280 Feature New 22/08/2019 00:00 -1 8280 Feature Approved 27/08/2019 00:00 -1 8280 Feature In Progress 08/09/2019 00:00 -1 8280 Feature Done 10/10/2019 00:00 -1 7840 Feature New 25/07/2019 00:00 4 7840 Feature Prioritized 30/07/2019 00:00 4 7840 Feature In Progress 07/08/2019 00:00 4 7840 Feature Done 30/10/2019 00:00 4 7817 Feature New 25/07/2019 00:00 4 7817 Feature Prioritized 30/07/2019 00:00 4 7817 Feature In Progress 07/08/2019 00:00 4 7817 Feature Done 29/10/2019 00:00 4 7707 Feature New 06/07/2019 00:00 7 7707 Feature Prioritized 14/07/2019 00:00 7 7707 Feature In Progress 23/07/2019 00:00 7 7707 Feature Done 10/10/2019 00:00 7 Anonymous
If you change the measure to remove the -1 then the datediff will return blank for those with no new or prioritized date but then the number of days don't match what you had in your first post but they do match what you had in your second post. The -1 was just there to force the calc to match your first post.
Date Diff = AVERAGEX( VALUES('MEDIAS STATUS'[Work Item Id]), (DATEDIFF( CALCULATE( MAX ( 'MEDIAS STATUS'[STATE DATE] ), ALLEXCEPT('MEDIAS STATUS','MEDIAS STATUS'[Work Item Id]),'MEDIAS STATUS'[State] = "New"), CALCULATE( MAX ( 'MEDIAS STATUS'[STATE DATE] ), ALLEXCEPT('MEDIAS STATUS','MEDIAS STATUS'[Work Item Id]),'MEDIAS STATUS'[State] = "Prioritized"), DAY ) ) )