Urgent Date interval same column

[Anonymous]

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

[jdbuchanan71]

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.

[Anonymous]

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 · Accepted solution]

@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 ) )
)