help with building sequence of dates

[apboudr1]

I’m stuck and would sincerely appreciate some friendly expert help 🙂

What I’m trying to do is calculate a column of “startdates” using other columns “campassign”, “camporder”, “duration”, and “stopdate”.

“Campassign”, “camporder”, and “duration” have entered data. “Stopdate” is equal to “startdate” + “duration”. For the first item in each “campassign” (“camporder”=1), I use lookupvalue to pull the entered “startdate” for the matching “campname” from another table. When order does not equal 1, I want to pull the “stopdate” from the previous item in the order within the matching “campassign” and use it as the “startdate”. I’ve tried doing this with another lookupvalue in the else clause of an IF statement, but I run into a circular reference issue. Perhaps there is a filter I need to add, a different function altogether, switch to a measure instead of calculated column?

Activities1
CampAssign	Duration	CampOrder	StartDate	StopDate
Alpha	14	1	STUCK!	= Activities1[StartDate]+Activities1[Duration]
Alpha	21	2
Alpha	14	3
Alpha	28	4
Bravo	62	1
Bravo	35	2
Bravo	62	3
Bravo	35	4
Charlie	50	1
Charlie	21	2
Campaigns2
CampName	StartDate
Alpha	1/1/2021
Bravo	4/1/2022
Charlie	6/1/2023

What I have now:

StartDate = if(Activities1[CampOrder]=1,LOOKUPVALUE(Campaigns2[StartDate],Campaigns2[CampName],Activities1[CampAssign]),LOOKUPVALUE(Activities1[StopDate],Activities1[CampAssign],Activities1[CampAssign],Activities1[CampOrder],(Activities1[CampOrder]-1)))

I was able to do this in excel fairly easily using index and match:

=IF([@CampOrder]=1,INDEX(Campaigns2[StartDate],MATCH([@CampAssign],Campaigns2[CampName],0)),INDEX([StopDate],

MATCH(1,([@CampAssign]=[CampAssign])*(([@CampOrder]-1)=[CampOrder]),0)

))

Thanks in advance!

[apboudr1 · Accepted solution]

Nevermind, I figured this out myself.

My solution below in case another has this problem:

Approached from a different angle. Instead of trying to pull the StopDate of the prior CampOrder, I used a running sum of durations (by CampAssign in CampOrder) and added that to the campaign StartDate, which gave me a StopDate for each Activity. From StopDate I can subtract the individual duration of each activity to get the StartDate of the Activity.

[amitchandak]

@apboudr1 , While I still not clear on all output you need -

The close date you should able to get as a new column in Activities1 like

maxx(filter(Campaigns2, Campaigns2[CampName] =Activities1[CampAssign]), Campaigns2[StartDate])+ Activities1[Duration]

[amitchandak]

@apboudr1 , where is stop_date in data. I think that is part of the calculation?

[apboudr1]

Stopdate is in the table and calculated as startdate+duration which leads to the circular referencing issue when I try to pull prior order stopdate as startdate