Calculate how much time issue spent in certain status to get leadtime

[Anonymous]

Hello,

I want to create a Report which shows the leadtime of each issue. But also you should be able to see how much time it was in a certain status. So it should look something like this: 

Now I made a query to get the needed data, it looks like this: 

The thing is, an issue can go into the statuses multiple times as you can see.  Created -> Backlog -> Analyzing -> Backlog -> Analyzing etc. 

What there should happen is, each time an issue goes into a certain status, it should get summed up for that status and calculated together in a new column so it looks something like this: 

I really hope someone can help me with this because i don't know how to do it. 

[vanessafvg]

what are you struggling with exactly?  not quite sure i understand what you are trying to do.  are you wanting to do this in the table or in the visual  It makes sense to sum it in a visual 

if you want to share your data in a text form or sample data 

[Anonymous]

oldvalue	oldstring	newvalue	newstring	id	issue_key	statuschangedate	issuecreated	issuelastupdated	BacklogTime	AnalyzingTime	ReadyForPiPlanning
10321	Backlog	10807	Analyzing	117816	EART-10	04.10.2018 13:35	01.10.2018 09:26	01.10.2019 11:22
10807	Analyzing	10808	Ready For PI Planning	117816	EART-10	04.10.2018 13:42	01.10.2018 09:26	01.10.2019 11:22
10808	Ready For PI Planning	10807	Analyzing	117816	EART-10	12.10.2018 14:06	01.10.2018 09:26	01.10.2019 11:22
10807	Analyzing	10808	Ready For PI Planning	117816	EART-10	12.10.2018 14:11	01.10.2018 09:26	01.10.2019 11:22
10808	Ready For PI Planning	11410	Actual PI	117816	EART-10	29.10.2018 10:20	01.10.2018 09:26	01.10.2019 11:22
11410	Actual PI	10111	Implementing	117816	EART-10	31.10.2018 11:36	01.10.2018 09:26	01.10.2019 11:22
10111	Implementing	10903	TVV	117816	EART-10	07.12.2018 15:56	01.10.2018 09:26	01.10.2019 11:22
10903	TVV	10906	Releasing	117816	EART-10	15.01.2019 09:39	01.10.2018 09:26	01.10.2019 11:22
10906	Releasing	10002	Done	117816	EART-10	18.01.2019 09:44	01.10.2018 09:26	01.10.2019 11:22
10321	Backlog	10807	Analyzing	118666	EART-1003	11.03.2019 15:24	11.03.2019 15:23	26.09.2019 08:09
10807	Analyzing	10321	Backlog	118666	EART-1003	26.03.2019 17:00	11.03.2019 15:23	26.09.2019 08:09
10321	Backlog	10807	Analyzing	118666	EART-1003	11.06.2019 16:38	11.03.2019 15:23	26.09.2019 08:09
10807	Analyzing	10808	Ready For PI Planning	118666	EART-1003	27.06.2019 07:57	11.03.2019 15:23	26.09.2019 08:09
10808	Ready For PI Planning	11410	Actual PI	118666	EART-1003	08.07.2019 10:14	11.03.2019 15:23	26.09.2019 08:09
11410	Actual PI	3	In Progress	118666	EART-1003	08.07.2019 10:16	11.03.2019 15:23	26.09.2019 08:09
3	In Progress	10111	Implementing	118666	EART-1003	08.07.2019 11:06	11.03.2019 15:23	26.09.2019 08:09
10111	Implementing	10905	Validating on Staging (Ready for Demo)	118666	EART-1003	19.09.2019 11:29	11.03.2019 15:23	26.09.2019 08:09
10905	Validating on Staging (Ready for Demo)	10002	Done	118666	EART-1003	26.09.2019 08:09	11.03.2019 15:23	26.09.2019 08:09
10321	Backlog	10807	Analyzing	118667	EART-1004	12.03.2019 11:01	12.03.2019 10:17	11.07.2019 11:44
10807	Analyzing	10808	Ready For PI Planning	118667	EART-1004	26.03.2019 18:59	12.03.2019 10:17	11.07.2019 11:44
10808	Ready For PI Planning	11410	Actual PI	118667	EART-1004	05.04.2019 13:04	12.03.2019 10:17	11.07.2019 11:44
11410	Actual PI	3	In Progress	118667	EART-1004	09.05.2019 12:00	12.03.2019 10:17	11.07.2019 11:44
3	In Progress	10904	Validating on Staging (In Progress)	118667	EART-1004	24.06.2019 22:15	12.03.2019 10:17	11.07.2019 11:44
10904	Validating on Staging (In Progress)	10002	Done	118667	EART-1004	02.07.2019 08:15	12.03.2019 10:17	11.07.2019 11:44
10321	Backlog	10002	Done	118668	EART-1005	13.03.2019 13:03	12.03.2019 10:21	13.03.2019 13:03

Here is the Data 

[v-rzhou-msft]

Hi @Anonymous ,

Do you want to catch the last status change date instead of issue created date when status goes back to backlog again?

I suggest you can try this code.

BacklogTime =
VAR _Lastchangedate =
    CALCULATE (
        MAX ( status_dates_better[statuschangedate] ),
        FILTER (
            ALLEXCEPT ( status_dates_better, status_dates_better[issue_key] ),
            status_dates_better[statuschangedate]
                < EARLIER ( status_dates_better[statuschangedate] )
        )
    )
RETURN
    IF (
        status_dates_better[oldstring] = "Backlog",
        DATEDIFF (
            IF (
                _Lastchangedate = BLANK (),
                status_dates_better[issuecreated],
                _Lastchangedate
            ),
            status_dates_better[statuschangedate],
            DAY
        )
    )

Result is as below.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Thank you! I will try this but its just a part of the whole solution I need. I dont know how get the previous changedate per issue_key so that i can calculate in each row how long it was in that status. Can you help me with that ? 

[Anonymous]

I struggle with the DAX Formula to get those timecalculations. 
Because when an Issue is created it goes by default first in the status Backlog. When it goes from Backlog into Analyzing you can just do a

IF(status_dates_better[oldstring]="Backlog", DATEDIFF(status_dates_better[issuecreated],status_dates_better[statuschangedate],DAY))

But when it goes back to backlog again, you cant use the CreatedDate when it leaves that status for the second time... Then you would have to say somehow take the date when it left that status for second time

And thats where my difficulty is.