Enter additional date row to column

[Anonymous]

Hello, 

I have a dataset with start date, date of update and end dates that I am trying to put into a gantt chart or some other time visualisation to see the journey of the incidents. 

The problem is, I can only use two date fields in the gantt chart (start date and end date) but I would like to visualise the whole journey (including all updates). 

I created an additional column with previous incident update, which starts with the incident start date and then looks at the previous update date, which I was using to calculate time between updates. 

I can use this column as the start date and the date of update as the end date to sequence the start of the incident up until the most recent update, but it unfortunately leaves off the date of closure.

Do you have any idea how best to combine this so i can have one visual which looks at the start date, each update and then closure?

The visual I am trying to show is a timeline of all of these steps (for each incident), colour coded by categories. 

Any help would be much appreciated!

Thanks 

[mahoneypat · Accepted solution]

Please see if this timeline visual better meets your needs.

https://www.youtube.com/watch?v=yNqcUcrMigY&feature=emb_logo

Regards,

Pat

[v-alq-msft]

Hi, @Anonymous 

A Gantt chart can be generalized as a type of bar chart, where each bar represents a section of data on a time axis. The key elements portrayed on this chart generally are tasks, resources, duration, progress, time-axis, completion and inter-dependencies. You may adjust your data to fit the visual.

For further information, you may refer to the folllowing article.

Schedule analysis using Gantt chart in Power BI Desktop 

Best Regards

Allan

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

[Anonymous]

Thanks very much for your reply. I am trying to use a Gantt chart. The issue I have is more with the structure of the data. 

I am trying to represent all steps along the journey in one incident. So if we take the example below. Incident ID Start Date and closed date are all in one table, then, addended to these tables are multiple updates. 

So for incident A, it started on 01/01/2019, there were three updates, and then it was closed on the 01/04/2019. 

Incident ID	Start Date	Incident Update ID	Incident Update Date	incident Closed Date
A	01/01/2019	12	02/01/2019	01/04/2019
A	01/01/2019	13	15/01/2019	01/04/2019
A	01/01/2019	14	23/03/2019	01/04/2019
B	02/01/2019	22	13/03/2019	20/03/2019
B	02/01/2019	23	15/03/2019	20/03/2019

I used the following formula to create an additional column that demotes the incident date and also includes the start date in one column. 

Incident Start Date = if(ISBLANK(CALCULATE(MAX(Safeguarding[Date of update]),ALLEXCEPT(Safeguarding,Safeguarding[Incident Record ID]),Safeguarding[Date of update]<EARLIER(Safeguarding[Date of update]))),Safeguarding[Date of incident or safeguarding concern],(CALCULATE(MAX(Safeguarding[Date of update]),ALLEXCEPT(Safeguarding,Safeguarding[Incident Record ID]),Safeguarding[Date of update]<EARLIER(Safeguarding[Date of update]))))

Incident ID	Start Date	Incident Update ID	Incident Update Date	incident Closed Date	"Incident Start Date"
A	01/01/2019	12	02/01/2019	01/04/2019	01/01/2019
A	01/01/2019	13	15/01/2019	01/04/2019	02/01/2019
A	01/01/2019	14	23/03/2019	01/04/2019	15/01/2019
B	02/01/2019	22	13/03/2019	20/03/2019
B	02/01/2019	23	15/03/2019	20/03/2019

This allows me to use the new Incident Start Date, combines with Incident update to model the journey from start date to most recent update. However, I can't include the final step (from last update to closure) as there are only two fields available for incident dates in Gantt chart visuals. 

I wondered whether, also, the data could be structured bettwe in import. Perhaps it would be more helpful to have seperate tables for incident record ID and the addended incident update records? Or whether there was a clever way to do this without starting again?

Hopefully this is making sense?

Thanks  

Dax