Create End Date Based on Next Task Start Date

[integracaoti]

Hi,

I'm a new Power BI user and I have the situation below (around 500+ rows and it continually grows).

Each Identification represents a different company (it's a registration ID), and every time an event takes place, we record it in our database (request issued, contract created, contract awaiting signature, contract signed), so it's really a step-by-step process with very different time/date ranges because it really depends on the client, we end up only saving the Start Date ([dataInicio]).

Inherently, the End Date is the next task's Start Date.

Therefore, my question is, how can I create an End Date column inheriting its values from the next activity's Start Date. Because in the end I want to create a timeline of events and create a Lead Time visualization.

I tried this which I found on the forum, after grouping by Identification and creating an Index column for each expanded table:

endDateRelatedNextTask = IF(CALCULATE(MIN(eventos[Index])) = MAX(eventos[Index]),NOW(), CALCULATE(MIN(eventos[dataInicio]),FILTER(eventos,eventos[Identification] = EARLIER(eventos[Identification]) && eventos[Index] = EARLIER(eventos[Index])+1)))

but it fired this:

A circular dependency was detected: eventos[endDateRelatedNextTask], 27be8294-902b-42c8-9948-d31633f644ae, eventos[endDateRelatedNextTask].

I couldn't find a solution for this Please help me!

Thank you in advance

[integracaoti]

@Ashish_Mathuryour formula worked LIKE A CHARM! Thank you so much, with your help I even managed to make more complex visualizations.

I'm also thumbing up @StefanoGrimaldi and @MattAllington because you guys helped me with other stuff as well! Thank you very much for your time!

[Ashish_Mathur]

You are welcome.

[Ashish_Mathur · Accepted solution]

Hi,

Does this calculated column formula work?

endDateRelatedNextTask = CALCULATE(MIN(eventos[dataInicio]),FILTER(eventos,eventos[Identification] = EARLIER(eventos[Identification]) && eventos[dataInicio] > EARLIER(eventos[dataInicio])))

Hope this helps.

[StefanoGrimaldi]

here a similar  post that could help you solve this: https://community.powerbi.com/t5/Desktop/Find-next-value-by-date/td-p/503446 solved. 

[MattAllington]

My video here should help you https://youtu.be/xN2IRXQ2CvI