Forum Discussion
How to do dynamical calculation based on multiple conditions and specific date?
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.
3 Replies
Hi Anonymous , I would appreciate if you could attach some sample data here.
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!
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
