Forum Discussion
Create End Date Based on Next Task Start Date
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
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.
5 Replies
- Ashish_MathurSuper User
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.
- integracaotiFrequent Visitor
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_MathurSuper User
You are welcome.
- MattAllingtonCommunity Champion
My video here should help you https://youtu.be/xN2IRXQ2CvI
- StefanoGrimaldiResident Rockstar
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.