powerbidaxpowerquerypowerbi-desktopm
Incident development between periods
I have incidents generated every day - opened that are resolved in same day or month or next month, etc. or removed to infinity period
I need to display status of incidents for particular month how many of them are still opened and how many of them are resolved
I need it to do in Power BI.
I created columns:
Days Between according the methodology: https://amitchandak.medium.com/power-query-get-all-dates-between-the-start-and-end-date-9ad6a84cf5f2
Bridging Period that checks incidents in the same month - opened/closed expressed by formula:
IF ( F_IncidentLast3Months[Created Date] = F_IncidentLast3Months[Days Between], "Opened", IF ( F_IncidentLast3Months[Resolved Date] = F_IncidentLast3Months[Days Between], "Closed", "" ) )
Same Period that checks incidents closed same day when they were also opened, expressed by formula:
IF ( F_IncidentLast3Months[Created Date] = F_IncidentLast3Months[Resolved Date], "Closed", "" )
I am not able to handle situation when incident is created for instance on 11.01.2023 and resolved on 15.03.2023 so I need to show the incident as opened in January 23 as well as in February 23 (at the end of February). So in general I need to show incidents as opened that are still not resolved in next periods.
Can you please give me some advice how to do that?
I have my own code below and I need to clarify that my final output should contain all columns from sourcing table and added extra columns "Resolved" and "Current Opened"
let
//I am sourcing from table with many columns where are also columns Created Date and Resolved Date
Source = database("server", [HierarchicalNavigation=true, Query=" select * from table;"]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Created_Date", type date}, {"Resolved_Date", type date}}),
RenameColumns = Table.TransformColumnNames(#"Changed Type", each Text.Replace(_, "_", " ")),
//I need to put here the logic I explained above before I do another transformations
//At the final I need to have all columns from sourcing table, not only Date, Resolved, CurrentOpened
#"Grouped Rows" = Table.Group(RenameColumns, {"Config Item Id"}, {{"Group by Config Item Id", each _, type table [Config Item Id=number, Config Item Type Code=text, Config Item Name=nullable text, Data Product Layer=nullable text, Owner Type=nullable text, Owner Name=nullable text, App Steward Type=nullable text, App Steward Name=nullable text, Ticket Url=nullable text, Ticket Id=nullable text, Summary Title=nullable text, Priority Incident=nullable text, Status=nullable text, Assignee Id=nullable text, Reporter Id=nullable text, Created Date=nullable date, Resolved Date=nullable date, Duration=nullable text]}}),
#"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn([Group by Config Item Id], "Index", 1)),
#"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"Custom"}),
#"Expanded Custom" = Table.ExpandTableColumn(#"Removed Other Columns", "Custom", {"Config Item Id", "Config Item Type Code", "Config Item Name", "Data Product Layer", "Owner Type", "Owner Name", "App Steward Type", "App Steward Name", "Ticket Url", "Ticket Id", "Summary Title", "Priority Incident", "Status", "Assignee Id", "Reporter Id", "Created Date", "Created Date Before", "Resolved Date", "Resolved Date After", "Duration", "Days Between", "Index"}, {"Config Item Id", "Config Item Type Code", "Config Item Name", "Data Product Layer", "Owner Type", "Owner Name", "App Steward Type", "App Steward Name", "Ticket Url", "Ticket Id", "Summary Title", "Priority Incident", "Status", "Assignee Id", "Reporter Id", "Created Date", "Created Date Before", "Resolved Date", "Resolved Date After", "Duration", "Days Between", "Index"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Expanded Custom",{{"Days Between", type date}, {"Created Date", type date}, {"Resolved Date", type date}, {"Created Date Before", type date}, {"Resolved Date After", type date}})
in
#"Changed Type1"
INPUT DATASET
| App Steward Name | App Steward Type | Assignee Id | Config Item Id | Config Item Name | Config Item Type Code | Ticket Id | Created Date | Resolved Date | Data Product Layer | Duration | Owner Name | Owner Type | Priority Incident | Reporter Id | Status | Summary Title | Ticket Url |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| Core banking system replacement | Squad | e_lhoust | 1 | T24.TDP | CO | ESCB-13526 | 07/12/23 | 07/12/23 | L1 | 4 | Enterprise Services Tribe | OrgUnit | Critical | mtax | Closed | SM: Chybějící Statická data v TRH (Available_balance) | https://jira.kb.cz/jira/browse/ESCB-13526 |
| Core banking system replacement | Squad | e_pstupk | 1 | T24.TDP | CO | ESCB-12991 | 07/12/23 | 09/12/23 | L1 | 0 | Enterprise Services Tribe | OrgUnit | Critical | e_pstupk | Closed | [Shop T24] Oprava SDS tabulky CATEGORY na PROD | https://jira.kb.cz/jira/browse/ESCB-12991 |
| Core banking system replacement | Squad | e_pstupk | 1 | T24.TDP | CO | ESCB-13195 | 08/12/23 | 10/12/23 | L1 | 40 | Enterprise Services Tribe | OrgUnit | Non-Critical | e_robruc | Opened | Nesmyslné balance v CRF | https://jira.kb.cz/jira/browse/ESCB-13195 |
| Core banking system replacement | Squad | roliva | 1 | T24.TDP | CO | ICM-30654 | 25/10/23 | 03/12/23 | L1 | 9 | Enterprise Services Tribe | OrgUnit | Non-Critical | JIRAUSER47719 | Closed | D_T24 wfdi_T24_SDS_2_LSS_LSS Failed(25.10.2023) | https://jira.kb.cz/jira/browse/ICM-30654 |
REQUIRED OUTPUT DATASET
| App Steward Name | App Steward Type | Assignee Id | Config Item Id | Config Item Name | Config Item Type Code | Ticket Id | Created Date | Resolved Date | Date Between | Opened | Closed | Data Product Layer | Duration | Owner Name | Owner Type | Priority Incident | Reporter Id | Status | Summary Title | Ticket Url |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| Core banking system replacement | Squad | e_lhoust | 1 | T24.TDP | CO | ESCB-13526 | 07/12/23 | 07/12/23 | 07/12/23 | Opened | Closed | L1 | 4 | Enterprise Services Tribe | OrgUnit | Critical | mtax | Closed | SM: Chybějící Statická data v TRH (Available_balance) | https://jira.kb.cz/jira/browse/ESCB-13526 |
| Core banking system replacement | Squad | e_pstupk | 1 | T24.TDP | CO | ESCB-12991 | 07/12/23 | 09/12/23 | 07/12/23 | Opened | L1 | 0 | Enterprise Services Tribe | OrgUnit | Critical | e_pstupk | Closed | [Shop T24] Oprava SDS tabulky CATEGORY na PROD | https://jira.kb.cz/jira/browse/ESCB-12991 | |
| Core banking system replacement | Squad | e_pstupk | 1 | T24.TDP | CO | ESCB-12991 | 07/12/23 | 09/12/23 | 08/12/23 | L1 | 0 | Enterprise Services Tribe | OrgUnit | Critical | e_pstupk | Closed | [Shop T24] Oprava SDS tabulky CATEGORY na PROD | https://jira.kb.cz/jira/browse/ESCB-12991 | ||
| Core banking system replacement | Squad | e_pstupk | 1 | T24.TDP | CO | ESCB-12991 | 07/12/23 | 09/12/23 | 09/12/23 | Closed | L1 | 0 | Enterprise Services Tribe | OrgUnit | Critical | e_pstupk | Closed | [Shop T24] Oprava SDS tabulky CATEGORY na PROD | https://jira.kb.cz/jira/browse/ESCB-12991 | |
| Core banking system replacement | Squad | e_pstupk | 1 | T24.TDP | CO | ESCB-13195 | 08/12/23 | 10/12/23 | 08/12/23 | Opened | L1 | 40 | Enterprise Services Tribe | OrgUnit | Non-Critical | e_robruc | Opened | Nesmyslné balance v CRF | https://jira.kb.cz/jira/browse/ESCB-13195 | |
| Core banking system replacement | Squad | e_pstupk | 1 | T24.TDP | CO | ESCB-13195 | 08/12/23 | 10/12/23 | 09/12/23 | L1 | 40 | Enterprise Services Tribe | OrgUnit | Non-Critical | e_robruc | Opened | Nesmyslné balance v CRF | https://jira.kb.cz/jira/browse/ESCB-13195 | ||
| Core banking system replacement | Squad | e_pstupk | 1 | T24.TDP | CO | ESCB-13195 | 08/12/23 | 10/12/23 | 10/12/23 | Closed | L1 | 40 | Enterprise Services Tribe | OrgUnit | Non-Critical | e_robruc | Opened | Nesmyslné balance v CRF | https://jira.kb.cz/jira/browse/ESCB-13195 | |
| Core banking system replacement | Squad | roliva | 1 | T24.TDP | CO | ICM-30654 | 25/10/23 | 03/12/23 | 25/10/23 | Opened | L1 | 9 | Enterprise Services Tribe | OrgUnit | Non-Critical | JIRAUSER47719 | Closed | D_T24 wfdi_T24_SDS_2_LSS_LSS Failed(25.10.2023) | https://jira.kb.cz/jira/browse/ICM-30654 | |
| Core banking system replacement | Squad | roliva | 1 | T24.TDP | CO | ICM-30654 | 25/10/23 | 03/12/23 | 26/10/23 | L1 | 9 | Enterprise Services Tribe | OrgUnit | Non-Critical | JIRAUSER47719 | Closed | D_T24 wfdi_T24_SDS_2_LSS_LSS Failed(25.10.2023) | https://jira.kb.cz/jira/browse/ICM-30654 | ||
| Core banking system replacement | Squad | roliva | 1 | T24.TDP | CO | ICM-30654 | 25/10/23 | 03/12/23 | 27/10/23 | L1 | 9 | Enterprise Services Tribe | OrgUnit | Non-Critical | JIRAUSER47719 | Closed | D_T24 wfdi_T24_SDS_2_LSS_LSS Failed(25.10.2023) | https://jira.kb.cz/jira/browse/ICM-30654 | ||
| Core banking system replacement | Squad | roliva | 1 | T24.TDP | CO | ICM-30654 | 25/10/23 | 03/12/23 | 28/10/23 | L1 | 9 | Enterprise Services Tribe | OrgUnit | Non-Critical | JIRAUSER47719 | Closed | D_T24 wfdi_T24_SDS_2_LSS_LSS Failed(25.10.2023) | https://jira.kb.cz/jira/browse/ICM-30654 | ||
| Core banking system replacement | Squad | roliva | 1 | T24.TDP | CO | ICM-30654 | 25/10/23 | 03/12/23 | 29/10/23 | L1 | 9 | Enterprise Services Tribe | OrgUnit | Non-Critical | JIRAUSER47719 | Closed | D_T24 wfdi_T24_SDS_2_LSS_LSS Failed(25.10.2023) | https://jira.kb.cz/jira/browse/ICM-30654 | ||
| Core banking system replacement | Squad | roliva | 1 | T24.TDP | CO | ICM-30654 | 25/10/23 | 03/12/23 | 30/10/23 | L1 | 9 | Enterprise Services Tribe | OrgUnit | Non-Critical | JIRAUSER47719 | Closed | D_T24 wfdi_T24_SDS_2_LSS_LSS Failed(25.10.2023) | https://jira.kb.cz/jira/browse/ICM-30654 | ||
| Core banking system replacement | Squad | roliva | 1 | T24.TDP | CO | ICM-30654 | 25/10/23 | 03/12/23 | 31/10/23 | L1 | 9 | Enterprise Services Tribe | OrgUnit | Non-Critical | JIRAUSER47719 | Closed | D_T24 wfdi_T24_SDS_2_LSS_LSS Failed(25.10.2023) | https://jira.kb.cz/jira/browse/ICM-30654 | ||
| Core banking system replacement | Squad | roliva | 1 | T24.TDP | CO | ICM-30654 | 25/10/23 | 03/12/23 | 01/11/23 | L1 | 9 | Enterprise Services Tribe | OrgUnit | Non-Critical | JIRAUSER47719 | Closed | D_T24 wfdi_T24_SDS_2_LSS_LSS Failed(25.10.2023) | https://jira.kb.cz/jira/browse/ICM-30654 | ||
| Core banking system replacement | Squad | roliva | 1 | T24.TDP | CO | ICM-30654 | 25/10/23 | 03/12/23 | 02/11/23 | L1 | 9 | Enterprise Services Tribe | OrgUnit | Non-Critical | JIRAUSER47719 | Closed | D_T24 wfdi_T24_SDS_2_LSS_LSS Failed(25.10.2023) | https://jira.kb.cz/jira/browse/ICM-30654 | ||
| Core banking system replacement | Squad | roliva | 1 | T24.TDP | CO | ICM-30654 | 25/10/23 | 03/12/23 | 03/11/23 | L1 | 9 | Enterprise Services Tribe | OrgUnit | Non-Critical | JIRAUSER47719 | Closed | D_T24 wfdi_T24_SDS_2_LSS_LSS Failed(25.10.2023) | https://jira.kb.cz/jira/browse/ICM-30654 | ||
| Core banking system replacement | Squad | roliva | 1 | T24.TDP | CO | ICM-30654 | 25/10/23 | 03/12/23 | 04/11/23 | L1 | 9 | Enterprise Services Tribe | OrgUnit | Non-Critical | JIRAUSER47719 | Closed | D_T24 wfdi_T24_SDS_2_LSS_LSS Failed(25.10.2023) | https://jira.kb.cz/jira/browse/ICM-30654 | ||
| Core banking system replacement | Squad | roliva | 1 | T24.TDP | CO | ICM-30654 | 25/10/23 | 03/12/23 | 05/11/23 | L1 | 9 | Enterprise Services Tribe | OrgUnit | Non-Critical | JIRAUSER47719 | Closed | D_T24 wfdi_T24_SDS_2_LSS_LSS Failed(25.10.2023) | https://jira.kb.cz/jira/browse/ICM-30654 | ||
| Core banking system replacement | Squad | roliva | 1 | T24.TDP | CO | ICM-30654 | 25/10/23 | 03/12/23 | 06/11/23 | L1 | 9 | Enterprise Services Tribe | OrgUnit | Non-Critical | JIRAUSER47719 | Closed | D_T24 wfdi_T24_SDS_2_LSS_LSS Failed(25.10.2023) | https://jira.kb.cz/jira/browse/ICM-30654 | ||
| Core banking system replacement | Squad | roliva | 1 | T24.TDP | CO | ICM-30654 | 25/10/23 | 03/12/23 | 07/11/23 | L1 | 9 | Enterprise Services Tribe | OrgUnit | Non-Critical | JIRAUSER47719 | Closed | D_T24 wfdi_T24_SDS_2_LSS_LSS Failed(25.10.2023) | https://jira.kb.cz/jira/browse/ICM-30654 | ||
| Core banking system replacement | Squad | roliva | 1 | T24.TDP | CO | ICM-30654 | 25/10/23 | 03/12/23 | 08/11/23 | L1 | 9 | Enterprise Services Tribe | OrgUnit | Non-Critical | JIRAUSER47719 | Closed | D_T24 wfdi_T24_SDS_2_LSS_LSS Failed(25.10.2023) | https://jira.kb.cz/jira/browse/ICM-30654 | ||
| Core banking system replacement | Squad | roliva | 1 | T24.TDP | CO | ICM-30654 | 25/10/23 | 03/12/23 | 09/11/23 | L1 | 9 | Enterprise Services Tribe | OrgUnit | Non-Critical | JIRAUSER47719 | Closed | D_T24 wfdi_T24_SDS_2_LSS_LSS Failed(25.10.2023) | https://jira.kb.cz/jira/browse/ICM-30654 | ||
| Core banking system replacement | Squad | roliva | 1 | T24.TDP | CO | ICM-30654 | 25/10/23 | 03/12/23 | 10/11/23 | L1 | 9 | Enterprise Services Tribe | OrgUnit | Non-Critical | JIRAUSER47719 | Closed | D_T24 wfdi_T24_SDS_2_LSS_LSS Failed(25.10.2023) | https://jira.kb.cz/jira/browse/ICM-30654 | ||
| Core banking system replacement | Squad | roliva | 1 | T24.TDP | CO | ICM-30654 | 25/10/23 | 03/12/23 | 11/11/23 | L1 | 9 | Enterprise Services Tribe | OrgUnit | Non-Critical | JIRAUSER47719 | Closed | D_T24 wfdi_T24_SDS_2_LSS_LSS Failed(25.10.2023) | https://jira.kb.cz/jira/browse/ICM-30654 | ||
| Core banking system replacement | Squad | roliva | 1 | T24.TDP | CO | ICM-30654 | 25/10/23 | 03/12/23 | 12/11/23 | L1 | 9 | Enterprise Services Tribe | OrgUnit | Non-Critical | JIRAUSER47719 | Closed | D_T24 wfdi_T24_SDS_2_LSS_LSS Failed(25.10.2023) | https://jira.kb.cz/jira/browse/ICM-30654 | ||
| Core banking system replacement | Squad | roliva | 1 | T24.TDP | CO | ICM-30654 | 25/10/23 | 03/12/23 | 13/11/23 | L1 | 9 | Enterprise Services Tribe | OrgUnit | Non-Critical | JIRAUSER47719 | Closed | D_T24 wfdi_T24_SDS_2_LSS_LSS Failed(25.10.2023) | https://jira.kb.cz/jira/browse/ICM-30654 | ||
| Core banking system replacement | Squad | roliva | 1 | T24.TDP | CO | ICM-30654 | 25/10/23 | 03/12/23 | 14/11/23 | L1 | 9 | Enterprise Services Tribe | OrgUnit | Non-Critical | JIRAUSER47719 | Closed | D_T24 wfdi_T24_SDS_2_LSS_LSS Failed(25.10.2023) | https://jira.kb.cz/jira/browse/ICM-30654 | ||
| Core banking system replacement | Squad | roliva | 1 | T24.TDP | CO | ICM-30654 | 25/10/23 | 03/12/23 | 15/11/23 | L1 | 9 | Enterprise Services Tribe | OrgUnit | Non-Critical | JIRAUSER47719 | Closed | D_T24 wfdi_T24_SDS_2_LSS_LSS Failed(25.10.2023) | https://jira.kb.cz/jira/browse/ICM-30654 | ||
| Core banking system replacement | Squad | roliva | 1 | T24.TDP | CO | ICM-30654 | 25/10/23 | 03/12/23 | 16/11/23 | L1 | 9 | Enterprise Services Tribe | OrgUnit | Non-Critical | JIRAUSER47719 | Closed | D_T24 wfdi_T24_SDS_2_LSS_LSS Failed(25.10.2023) | https://jira.kb.cz/jira/browse/ICM-30654 | ||
| Core banking system replacement | Squad | roliva | 1 | T24.TDP | CO | ICM-30654 | 25/10/23 | 03/12/23 | 17/11/23 | L1 | 9 | Enterprise Services Tribe | OrgUnit | Non-Critical | JIRAUSER47719 | Closed | D_T24 wfdi_T24_SDS_2_LSS_LSS Failed(25.10.2023) | https://jira.kb.cz/jira/browse/ICM-30654 | ||
| Core banking system replacement | Squad | roliva | 1 | T24.TDP | CO | ICM-30654 | 25/10/23 | 03/12/23 | 18/11/23 | L1 | 9 | Enterprise Services Tribe | OrgUnit | Non-Critical | JIRAUSER47719 | Closed | D_T24 wfdi_T24_SDS_2_LSS_LSS Failed(25.10.2023) | https://jira.kb.cz/jira/browse/ICM-30654 | ||
| Core banking system replacement | Squad | roliva | 1 | T24.TDP | CO | ICM-30654 | 25/10/23 | 03/12/23 | 19/11/23 | L1 | 9 | Enterprise Services Tribe | OrgUnit | Non-Critical | JIRAUSER47719 | Closed | D_T24 wfdi_T24_SDS_2_LSS_LSS Failed(25.10.2023) | https://jira.kb.cz/jira/browse/ICM-30654 | ||
| Core banking system replacement | Squad | roliva | 1 | T24.TDP | CO | ICM-30654 | 25/10/23 | 03/12/23 | 20/11/23 | L1 | 9 | Enterprise Services Tribe | OrgUnit | Non-Critical | JIRAUSER47719 | Closed | D_T24 wfdi_T24_SDS_2_LSS_LSS Failed(25.10.2023) | https://jira.kb.cz/jira/browse/ICM-30654 | ||
| Core banking system replacement | Squad | roliva | 1 | T24.TDP | CO | ICM-30654 | 25/10/23 | 03/12/23 | 21/11/23 | L1 | 9 | Enterprise Services Tribe | OrgUnit | Non-Critical | JIRAUSER47719 | Closed | D_T24 wfdi_T24_SDS_2_LSS_LSS Failed(25.10.2023) | https://jira.kb.cz/jira/browse/ICM-30654 | ||
| Core banking system replacement | Squad | roliva | 1 | T24.TDP | CO | ICM-30654 | 25/10/23 | 03/12/23 | 22/11/23 | L1 | 9 | Enterprise Services Tribe | OrgUnit | Non-Critical | JIRAUSER47719 | Closed | D_T24 wfdi_T24_SDS_2_LSS_LSS Failed(25.10.2023) | https://jira.kb.cz/jira/browse/ICM-30654 | ||
| Core banking system replacement | Squad | roliva | 1 | T24.TDP | CO | ICM-30654 | 25/10/23 | 03/12/23 | 23/11/23 | L1 | 9 | Enterprise Services Tribe | OrgUnit | Non-Critical | JIRAUSER47719 | Closed | D_T24 wfdi_T24_SDS_2_LSS_LSS Failed(25.10.2023) | https://jira.kb.cz/jira/browse/ICM-30654 | ||
| Core banking system replacement | Squad | roliva | 1 | T24.TDP | CO | ICM-30654 | 25/10/23 | 03/12/23 | 24/11/23 | L1 | 9 | Enterprise Services Tribe | OrgUnit | Non-Critical | JIRAUSER47719 | Closed | D_T24 wfdi_T24_SDS_2_LSS_LSS Failed(25.10.2023) | https://jira.kb.cz/jira/browse/ICM-30654 | ||
| Core banking system replacement | Squad | roliva | 1 | T24.TDP | CO | ICM-30654 | 25/10/23 | 03/12/23 | 25/11/23 | L1 | 9 | Enterprise Services Tribe | OrgUnit | Non-Critical | JIRAUSER47719 | Closed | D_T24 wfdi_T24_SDS_2_LSS_LSS Failed(25.10.2023) | https://jira.kb.cz/jira/browse/ICM-30654 | ||
| Core banking system replacement | Squad | roliva | 1 | T24.TDP | CO | ICM-30654 | 25/10/23 | 03/12/23 | 26/11/23 | L1 | 9 | Enterprise Services Tribe | OrgUnit | Non-Critical | JIRAUSER47719 | Closed | D_T24 wfdi_T24_SDS_2_LSS_LSS Failed(25.10.2023) | https://jira.kb.cz/jira/browse/ICM-30654 | ||
| Core banking system replacement | Squad | roliva | 1 | T24.TDP | CO | ICM-30654 | 25/10/23 | 03/12/23 | 27/11/23 | L1 | 9 | Enterprise Services Tribe | OrgUnit | Non-Critical | JIRAUSER47719 | Closed | D_T24 wfdi_T24_SDS_2_LSS_LSS Failed(25.10.2023) | https://jira.kb.cz/jira/browse/ICM-30654 | ||
| Core banking system replacement | Squad | roliva | 1 | T24.TDP | CO | ICM-30654 | 25/10/23 | 03/12/23 | 28/11/23 | L1 | 9 | Enterprise Services Tribe | OrgUnit | Non-Critical | JIRAUSER47719 | Closed | D_T24 wfdi_T24_SDS_2_LSS_LSS Failed(25.10.2023) | https://jira.kb.cz/jira/browse/ICM-30654 | ||
| Core banking system replacement | Squad | roliva | 1 | T24.TDP | CO | ICM-30654 | 25/10/23 | 03/12/23 | 29/11/23 | L1 | 9 | Enterprise Services Tribe | OrgUnit | Non-Critical | JIRAUSER47719 | Closed | D_T24 wfdi_T24_SDS_2_LSS_LSS Failed(25.10.2023) | https://jira.kb.cz/jira/browse/ICM-30654 | ||
| Core banking system replacement | Squad | roliva | 1 | T24.TDP | CO | ICM-30654 | 25/10/23 | 03/12/23 | 30/11/23 | Opened | L1 | 9 | Enterprise Services Tribe | OrgUnit | Non-Critical | JIRAUSER47719 | Closed | D_T24 wfdi_T24_SDS_2_LSS_LSS Failed(25.10.2023) | https://jira.kb.cz/jira/browse/ICM-30654 | |
| Core banking system replacement | Squad | roliva | 1 | T24.TDP | CO | ICM-30654 | 25/10/23 | 03/12/23 | 01/12/23 | L1 | 9 | Enterprise Services Tribe | OrgUnit | Non-Critical | JIRAUSER47719 | Closed | D_T24 wfdi_T24_SDS_2_LSS_LSS Failed(25.10.2023) | https://jira.kb.cz/jira/browse/ICM-30654 | ||
| Core banking system replacement | Squad | roliva | 1 | T24.TDP | CO | ICM-30654 | 25/10/23 | 03/12/23 | 02/12/23 | L1 | 9 | Enterprise Services Tribe | OrgUnit | Non-Critical | JIRAUSER47719 | Closed | D_T24 wfdi_T24_SDS_2_LSS_LSS Failed(25.10.2023) | https://jira.kb.cz/jira/browse/ICM-30654 | ||
| Core banking system replacement | Squad | roliva | 1 | T24.TDP | CO | ICM-30654 | 25/10/23 | 03/12/23 | 03/12/23 | Closed | L1 | 9 | Enterprise Services Tribe | OrgUnit | Non-Critical | JIRAUSER47719 | Closed | D_T24 wfdi_T24_SDS_2_LSS_LSS Failed(25.10.2023) | https://jira.kb.cz/jira/browse/ICM-30654 |
Solution
Edited to address your revised problem statement
Your revised output table, from your revised sample, can be addressed by the following code, I think:
Note: this handles non-closed incidents by assuming that the an incident will not be shown as Closed if it is resolved in the future or if the Resolved Date is prior to Created Date.
let
//change next lines to reflect actual data source and column headers
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{
{"App Steward Name", type text}, {"App Steward Type", type text}, {"Assignee Id", type text},
{"Config Item Id", Int64.Type}, {"Config Item Name", type text}, {"Config Item Type Code", type text},
{"Ticket Id", type text}, {"Created Date", type date}, {"Resolved Date", type date},
{"Data Product Layer", type text}, {"Duration", Int64.Type}, {"Owner Name", type text},
{"Owner Type", type text}, {"Priority Incident", type text}, {"Reporter Id", type text},
{"Status", type text}, {"Summary Title", type text}, {"Ticket Url", type text}}),
//Date Between column will have a maximum date of "Today"
//Resolved Dates that are after TODAY are ignored
//Resolved Dates that are before Created Date are ignored
#"Add Date Between" = Table.AddColumn(#"Changed Type", "Date Between", each
List.Dates(
[Created Date],
Duration.Days(
List.Min({
if [Resolved Date] < [Created Date]
then Date.From(DateTime.FixedLocalNow())
else [Resolved Date]
,
Date.From(DateTime.FixedLocalNow())})-[Created Date])+1,
#duration(1,0,0,0)),
type {date}),
#"Expanded Date Between" = Table.ExpandListColumn(#"Add Date Between", "Date Between"),
//Index column added to preserve original data order
#"Add Index" = Table.AddIndexColumn(#"Expanded Date Between","sortIndex",0,1,Int64.Type),
//Rows grouped by Ticket ID
//You could group by additional columns if necessary
#"Grouped Rows" = Table.Group(#"Add Index", {"Ticket Id"}, {
{"Closed/Open", (t)=>let
//Note that Date Between will never = Resolved date if Resolved date is in the future
#"Add Closed" = Table.AddColumn(#"t","Closed",
each if [Date Between]=[Resolved Date] then "Closed" else null, type text),
//Opened date should always be the first date in the list which is the beginning of the Created Date list
#"Add Opened" = Table.AddColumn(#"Add Closed","Opened",
each if [Date Between]=[Created Date] then "Opened" else null, type text),
#"First Month Start" = Date.StartOfMonth(#"Add Opened"[Date Between]{0}),
//If the End Of Month "Open" slot is not in the first month, then print "Open" there.
// UNLESS it is Closed on that date
#"EOM Open" = Table.ReplaceValue(
#"Add Opened",
each [Date Between],//y
each [Closed], //z
(x,y,z)=> if y = Date.EndOfMonth(y)
and Date.StartOfMonth(y) <> #"First Month Start"
and z <> "Closed"
then "Opened"
else x,
{"Opened"} //x
)
in #"EOM Open"}}),
#"Removed Columns" = Table.RemoveColumns(#"Grouped Rows",{"Ticket Id"}),
#"Expanded Closed/Open" = Table.ExpandTableColumn(#"Removed Columns", "Closed/Open", {"App Steward Name", "App Steward Type", "Assignee Id", "Config Item Id", "Config Item Name", "Config Item Type Code", "Ticket Id", "Created Date", "Resolved Date", "Data Product Layer", "Duration", "Owner Name", "Owner Type", "Priority Incident", "Reporter Id", "Status", "Summary Title", "Ticket Url", "Date Between", "sortIndex", "Closed", "Opened"}),
#"Sorted Rows" = Table.Sort(#"Expanded Closed/Open",{{"sortIndex", Order.Ascending}}),
#"Removed Columns1" = Table.RemoveColumns(#"Sorted Rows",{"sortIndex"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Removed Columns1",{{"App Steward Name", type text}, {"App Steward Type", type text}, {"Assignee Id", type text}, {"Config Item Id", Int64.Type}, {"Config Item Name", type text}, {"Config Item Type Code", type text}, {"Ticket Id", type text}, {"Created Date", type date}, {"Resolved Date", type date}, {"Data Product Layer", type text}, {"Duration", Int64.Type}, {"Owner Name", type text}, {"Owner Type", type text}, {"Priority Incident", type text}, {"Reporter Id", type text}, {"Status", type text}, {"Summary Title", type text}, {"Ticket Url", type text}, {"Date Between", type date}, {"Closed", type text}, {"Opened", type text}}),
#"Reordered Columns" = Table.ReorderColumns(#"Changed Type1",{"App Steward Name", "App Steward Type", "Assignee Id", "Config Item Id", "Config Item Name", "Config Item Type Code", "Ticket Id", "Created Date", "Resolved Date", "Date Between", "Opened", "Closed", "Data Product Layer", "Duration", "Owner Name", "Owner Type", "Priority Incident", "Reporter Id", "Status", "Summary Title", "Ticket Url"})
in
#"Reordered Columns"
Output from your second data set
Note: This screenshot shows only a few columns, but ALL columns are retained
