Having two tables reference off of each other, one column for each table that will do that

[Anonymous]

I'm trying to create a projected deadlines column but each tasks date is dependent on the previous task and there are set # of days itll take for each task to be completed. I was able to create a calculated deadline utilizing the logic above but it cannot go farther than the last completed deadline so I was looking to have my new projected deadline (to show when the rest of the project could be completed if everything goes well) reference itself in a column but thats giving me a 'circular dependency'. So I wanted to know if I would have the same problem if I were to do it in separate tables and have a one column in each to reference each other instead (technically still doing the same work just in another table and having it reference off of different table instead)

[Anonymous]

So realistically, each task can only be completed if the prior ones before it were completed as well. The order is different on what task is dependent on another so I have it set where I made them unique identifiers using an ID. The Calculated deadline is this DAX code - 

Calculated Deadline = IF ( NOT(ISBLANK('RFP_Milestone (Calc)'[Dependent Completed Dates])|| 'RFP_Milestone (Calc)'[Dependent Completed Dates] = ""),DATEVALUE('RFP_Milestone (Calc)'[Dependent Completed Dates]) + VALUE('RFP_Milestone (Calc)'[# of Days]))

Which brings in the correct deadlines meant to be followed as we complete each tasks but now I want do a projected deadline but I'm stuck on having it reference the calculated deadlines which dont have all of the dates filled in. 

Projected deadline DAX Code is -

 

Projected Deadline = IF(ISBLANK('RFP_Milestone (Calc)'[completedon].[Date]), IF (NOT(ISBLANK('RFP_Milestone (Calc)'[Dependent Completed Dates])|| 'RFP_Milestone (Calc)'[Dependent Completed Dates] = ""),VALUE('RFP_Milestone (Calc)'[Dependent Completed Dates] + VALUE('RFP_Milestone (Calc)'[# of Days])), VALUE('RFP_Milestone (Calc)'[Calculated Dependent Deadline] + VALUE('RFP_Milestone (Calc)'[# of Days]))), 'RFP_Milestone (Calc)'[completedon].[Date])

And the calculated depedent deadline is just a LookupValue that pulls the dependent dates. 

And this is where I was thinking I can have it reference itself somehow because as it calculates the new deadline it can then reference itself as a dependent date as well to then create another deadline afterwards. I hope I'm making sense. 

[tamerj1]

@Anonymous 
Can you please paste some sample data to work with?

[tamerj1]

Hi @Anonymous 

This is a complex subject as dax does not support recursive calculations. Nevertheless, this is sometimes possible ising some mathematics. Usually it is expensive in terms of performance. Please share more details and I'll see if anything can be done.