Incident and handover tracker using BI

[Crouch119]

Hi all,

Vairly new to BI, only really used it for visuals but looking deeper thought it would be a great tool to create a handover tracker.

Currently trying to make a report where I can check against two tables, one that will stay the same and another that will be updated daily. Asset inventory table and the incident table (past month).

Want to check if there has been an incident raised in the past 7 days from the install date (located in asset table).

If no incident past 7 days or there has been an incident but closed within the 7 days post the install date, post a new table with the asset name (new table?). 

If there is an incident raised on the 7th day, add an extra 2 days to warrenty and place into new CMDB table.

Currently build the relationship between the two tables and appended into a new table, currently reciveing a number of nulls.

Any help would be greatly apprecetative! 

Thanks

[amitchandak]

@Crouch119 ,Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

[Crouch119]

@amitchandak thank you for the quick response, I've created some sample data:

Say I run the report on 19/06/2020

I have 2 sheets in excel

1st sheet - Inventory (Dataset that doesn't change)

Name	Installed
ASSET1	2020-06-12 00:00:00
ASSET2	2020-06-12 00:00:00
ASSET3	2020-06-12 00:00:00
ASSET4	2020-06-13 00:00:00
ASSET5	2020-06-12 00:00:00
ASSET6	2020-06-12 00:00:00
ASSET7	2020-06-17 00:00:00
ASSET8	2020-06-06 00:00:00
ASSET9	2020-06-06 00:00:00
ASSET10	2020-06-13 00:00:00
ASSET11	2020-06-06 00:00:00
ASSET12	2020-06-06 00:00:00
ASSET13	TBC
ASSET14	TBC
ASSET15	TBC
ASSET16	TBC
ASSET17	TBC
ASSET18	TBC
ASSET19	TBC
ASSET20	TBC

2nd sheet - Incident (Daily extract imported into this sheet)

Number	Name	Open	Closed
INC001	ASSET1	2020-06-13 00:00:00	2020-06-15 00:00:00
INC002	ASSET2	2020-06-15 00:00:00	2020-06-18 00:00:00
INC003	ASSET3	2020-06-17 00:00:00	2020-06-17 00:00:00
INC004	ASSET4	2020-06-18 00:00:00	2020-06-18 00:00:00
INC005	ASSET1	2020-06-15 00:00:00	2020-06-15 00:00:00
INC006	ASSET10	2020-06-19 00:00:00
INC007	ASSET9	2020-06-08 00:00:00	2020-08-09 00:00:00
INC008	ASSET5	2020-06-09 00:00:00	2020-06-12 00:00:00
INC009	ASSET6	2020-06-14 00:00:00	2020-06-17 00:00:00
INC010	SOFTWARE	2020-06-08 00:00:00	2020-06-09 00:00:00
INC011	SOFTWARE2	2020-06-10 00:00:00
INC012	SOFTWARE2	2020-06-12 00:00:00
INC013	BUILDING1	2020-06-20 00:00:00
INC014	DRIVER1	2020-06-16 00:00:00	2020-06-18 00:00:00
INC015	BUILDING3	2020-06-05 00:00:00
INC016	BUILDING4	2020-06-09 00:00:00
INC017	BUILDING5	2020-06-06 00:00:00
INC018	ASSET2	2020-06-09 00:00:00	2020-06-14 00:00:00
INC019	ASSET2	2020-06-10 00:00:00	2020-05-14 00:00:00

3rd - Result (example of what the result report could look like)

Name	Ready to handover (Y / N)
ASSET1	Y
ASSET2	N
ASSET3	Y
ASSET4	N
ASSET5	Y
ASSET6	Y
ASSET7	N
ASSET8	N
ASSET9	Y
ASSET10	N
ASSET11	Y
ASSET12	Y
ASSET13	N
ASSET14	N
ASSET15	N
ASSET16	N
ASSET17	N
ASSET18	N
ASSET19	N
ASSET20	N

[V-lianl-msft]

Hi @Crouch119 ,

Based on the sample data you provided, what are your expected results?

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

[Crouch119]

Hi @V-lianl-msft I've editted the dataset to make it clearer.

Hope that helps?

Thanks

[V-lianl-msft]

Hi @Crouch119 ,

Sorry for the late reply.Based on the dataset you provided, I can't get the results you expect.

See if these can help you:

https://community.powerbi.com/t5/Desktop/Burnup-Chart-for-Milestones-Tracking/td-p/535109 

https://datamonkeysite.com/2019/10/05/engineering-progress-report-powerbi/ 

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

[Crouch119]

@amitchandak

@V-lianl-msft 

Any help would be greatly appreciated!