Forum Discussion
Urgent Date interval same column
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 ) ) )
Super UserHello 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 |
- jdbuchanan71
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 ) ) )