Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Solved! Go to 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.
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.
@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]
@apboudr1 , where is stop_date in data. I think that is part of the calculation?
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
45 | |
26 | |
22 | |
13 | |
8 |
User | Count |
---|---|
73 | |
51 | |
45 | |
16 | |
12 |