Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
I have two tables
OppTable
Opp id | statecodename | Actual close date |
1 | lost | 5-7-2016 |
2 | open | 5-9-2015 |
3 | won | 10-5-2016 |
updatesstagechange Table
Custom_opportunityid | stage | createdon | nextstagestartdate | Oppstagerank |
1 | prospect | 1-1-2016 | 2-1-2016 | 1 |
1 | qualify | 2-1-2016 | 5-7-2016(Actual close date from Opptable) | 2 |
2 | prospect | 3-1-2016 | 4-1-2016 | 1 |
2 | qualify | 4-1-2016 | 5-1-2016 | 2 |
2 | develop | 5-1-2016 | NOW DAte | 3 |
3 | Prospect | 6-1-2016 | 7-1-2016 | 1 |
3 | Qualify | 7-1-2016 | 8-1-2016 | 2 |
3 | develop | 8-1-2016 | 10-5-2016(Actual close date from Opptable) | 3 |
How to do it , if I want to make a condition if opportunity is lost or won, then the next blank record in updatesstagechange Table will take the actual close date from OppTable, else if open it takes today date
I wrote this column but its putting today date for all close, open and lost
NextStageStart date = IF(ISBLANK(LOOKUPVALUE(updatesstagechange[createdon],Updatesstagechange[Custom_opportunityid],Updatesstagechange[Custom_opportunityid],Updatesstagechange[OppStageRank],Updatesstagechange[OppStageRank]+1)),NOW(),LOOKUPVALUE(Updatesstagechange[createdon],Updatesstagechange[Custom_opportunityid],Updatesstagechange[Custom_opportunityid],Updatesstagechange[OppStageRank],Updatesstagechange[OppStageRank]+1))
OppStageRank = COUNTROWS( FILTER(
updatesstagechange,
updatesstagechange[custom_opportunityid]<>""&&
updatesstagechange[custom_opportunityid]=EARLIER(updatesstagechange[custom_opportunityid])&&
updatesstagechange[createdon]<EARLIER(updatesstagechange[createdon])
)
)+1
In your expression:
LOOKUPVALUE(updatesstagechange[createdon],Updatesstagechange[Custom_opportunityid],Updatesstagechange[Custom_opportunityid],Updatesstagechange[OppStageRank],Updatesstagechange[OppStageRank]+1)
The current row "Updatesstagechange[Custom_opportunityid]" can never equal to "Updatesstagechange[Custom_opportunityid]+1". So your "NextStageStart date" will always return NOW().
Based on your description, you want to show different date based on Opp ID. Your expression should be like below:
NextStageStart date = IF ( updatesstagechange[Custom_opportunityid] = 2, NOW (), LOOKUPVALUE ( Opp[Actual close date], Opp[Opp id], updatesstagechange[Custom_opportunityid] ) )
Regards,
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |