How to do dynamical calculation based on multiple conditions and specific date?

[TG-516]

I want to :

1. when incidents closed before March 31, 2023, I need to identify incidents that completed the "Inv+Act" phase(workflow phase name) <= 60days 

• Blue: ≤ 60 days 
• Orage: > 60 days 

2. when incidents closed after March 31, 2023, I need to identify incidents that completed the "Inv+Act" phase(workflow phase name) <= 90days 

• Dark Blue: ≤ 90 days 
• Dark Red: > 90 days 

3. Include the percentage of Closed incidents that completed Inv+Act<=60days/ 90 days

Example chart (but haven't added percentage line yet)

Some sample data:

Note: If there are multiple investigations, sum up the durations in all Inv+Act Phase.

How can I do above in PowerBI? Thank you for your help in advance.

[TG-516]

Sample data:

Columns in order: Incident ID, Incident Number, Workflow Phase Name, Submitted Date, Due Date, Days to Complete Phase

64	NC-2022-00064	Review	3/5/2023	3/7/2023	4
64	NC-2022-00064	Closed	3/9/2023	null	0
68	NC-2022-00068	Review	3/7/2023	3/9/2023	2
68	NC-2022-00068	Closed	3/9/2023	null	0
71	NC-2022-00071	Review	3/14/2023	3/16/2023	2
71	NC-2022-00071	Closed	3/16/2023	null	0
80	NC-2022-00080	Review	3/6/2023	3/8/2023	3
80	NC-2022-00080	Closed	3/9/2023	null	0
82	NC-2022-00082	Review	3/8/2023	3/10/2023	1
82	NC-2022-00082	Closed	3/9/2023	null	0
84	NC-2022-00084	Inv + Act	3/8/2023	2/17/2023	11
84	NC-2022-00084	Review	3/19/2023	3/21/2023	2
84	NC-2022-00084	Closed	3/21/2023	null	0
110	NC-2023-00016	Review	3/9/2023	3/11/2023	4
110	NC-2023-00016	Closed	3/13/2023	null	0
120	NC-2023-00026	Review	3/5/2023	3/7/2023	0
120	NC-2023-00026	Inv + Act	3/5/2023	4/7/2023	10
120	NC-2023-00026	Review	3/15/2023	3/17/2023	4
120	NC-2023-00026	Closed	3/19/2023	null	0
121	NC-2023-00027	Review	3/2/2023	3/4/2023	3
121	NC-2023-00027	Closed	3/5/2023	null	0
124	NC-2023-00030	Review	3/2/2023	3/4/2023	0
124	NC-2023-00030	Inv + Act	3/2/2023	4/4/2023	61
126	NC-2023-00032	Closed	3/2/2023	null	0
127	NC-2023-00033	Review	3/19/2023	3/21/2023	2
127	NC-2023-00033	Closed	3/21/2023	null	0
128	NC-2023-00034	Review	3/19/2023	3/21/2023	24
129	NC-2023-00035	Closed	3/1/2023	null	0
132	NC-2023-00038	Inv + Act	3/13/2023	3/20/2023	9
132	NC-2023-00038	Review	3/22/2023	3/24/2023	1
132	NC-2023-00038	Closed	3/23/2023	null	0
134	NC-2023-00040	Review	3/8/2023	3/10/2023	5
134	NC-2023-00040	Closed	3/13/2023	null	0
135	NC-2023-00041	Review	3/26/2023	3/28/2023	2
135	NC-2023-00041	Closed	3/28/2023	null	0
137	NC-2023-00043	Review	3/29/2023	3/31/2023	5
137	NC-2023-00043	Closed	4/3/2023	null	0
138	NC-2023-00044	Review	3/28/2023	3/30/2023	0
138	NC-2023-00044	Closed	3/28/2023	null	0
139	NC-2023-00045	Review	3/29/2023	3/31/2023	7
140	NC-2023-00046	Assign	3/8/2023	3/11/2023	1
140	NC-2023-00046	Inv + Act	3/9/2023	4/11/2023	75
142	NC-2023-00048	Review	3/15/2023	3/17/2023	1
142	NC-2023-00048	Inv + Act	3/16/2023	4/18/2023	12
142	NC-2023-00048	Review	3/28/2023	3/30/2023	6
142	NC-2023-00048	Closed	4/3/2023	null	0

[Akshaan]

Hi @TG-516 , I would appreciate if you could attach some sample data here.

[TG-516]

Hi @Akshaan, I am not sure if you can see my reply with sample data. Thank you for taking the time to look at it. Much appreciated!