To find out the first contact date following the action date

[uc]

Hello

I am new to Power BI. I need some help with the formula to find out first contact date following the Action date. Below is my table. There will be only one action date associated with an ID. ID can have different contct date. I would like to add a calculated column find out the first contact date following the action date as shown in the example below. Can some one help please? Thanks

Contact date	ID	Action date	First contact date following the Action date (what I want to achieve)
26/03/2021	1	01/03/2021	26/03/2021
01/04/2021	1	01/03/2021	26/03/2021
22/02/2021	1	01/03/2021	26/03/2021
09/04/2021	2	05/04/2021	09/04/2021
12/04/2021	2	05/04/2021	09/04/2021
15/04/2021	2	05/04/2021	09/04/2021
30/03/2021	2	05/04/2021	09/04/2021

[Jihwan_Kim · Accepted solution]

Hi, @uc 

Thank you for your feedback.

Please try the below.

First Contact Date After Action Date Calculated Column =

VAR currentid = 'Table'[ID]

VAR actiondate = 'Table'[Action date]

RETURN

CALCULATE (

MIN ( 'Table'[Contact date] ),

FILTER (

'Table',

'Table'[ID] = currentid

&& 'Table'[Contact date] > actiondate

)

)

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Linkedin: linkedin.com/in/jihwankim1975/

Twitter: twitter.com/Jihwan_JHKIM

[Jihwan_Kim]

Hi, @uc 

Please correct me if I wrongly understood your question, but I think the ID=1's first contact date is 22nd Feb 2021, and the ID=2's first contact date is 30th Mar 2021.

First Contact Date Calculated Column =
VAR currentid = 'Table'[ID]
RETURN
CALCULATE (
MIN ( 'Table'[Contact date] ),
FILTER ( 'Table', 'Table'[ID] = currentid )
)

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Linkedin: linkedin.com/in/jihwankim1975/

Twitter: twitter.com/Jihwan_JHKIM

[uc]

Hi Kim

Thanks for your reply but I am not looking for the first contact date. Sorry, looks like I haven't explained very well. I am looking for the first contact date which is after the action date. So in the above example, for ID 1, the first contact  date after the action date (which is 1/03/2021) will be 26/03/2021 and for ID 2 (action date is 5/04/2021) will be 9/04/2021. Hope I have explained now. Any help is appreciated. Thanks

[Jihwan_Kim · Accepted solution]

Hi, @uc 

Thank you for your feedback.

Please try the below.

First Contact Date After Action Date Calculated Column =

VAR currentid = 'Table'[ID]

VAR actiondate = 'Table'[Action date]

RETURN

CALCULATE (

MIN ( 'Table'[Contact date] ),

FILTER (

'Table',

'Table'[ID] = currentid

&& 'Table'[Contact date] > actiondate

)

)

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Linkedin: linkedin.com/in/jihwankim1975/

Twitter: twitter.com/Jihwan_JHKIM

[uc]

Hi Kim

I just realised that for some records there is no action date. In such scenarios the first contact date after action date needs to be blank and it isn't at the moment as shown below

Can you please help with the code please? Thanks

[Jihwan_Kim · Accepted solution]

Hi, @uc 

Please check the below.

I could not test the below Calcualted Column because I do not have a sample that contains blank action date.

Please check if it suits your case.

First Contact Date After Action Date Calculated Column =
VAR currentid = 'Table'[ID]
VAR actiondate = 'Table'[Action date]
RETURN
IF (
NOT ISBLANK ( 'Table'[Action date] ),
CALCULATE (
MIN ( 'Table'[Contact date] ),
FILTER (
'Table',
'Table'[ID] = currentid
&& 'Table'[Contact date] > actiondate
)
),
BLANK ()
)

[uc]

It works Kim 🙂. Thanks a lot!!

[uc]

It works! Thanks a lot Kim 🙂. Timely help is much appreciated.

[Anonymous]

Quick thought 

First contact = 

var lookupID=yourtable[id]

Calculate(min(contact date),filter(your table, [ID] =lookupid 

[uc]

Hi thanks for your reply. This formula helps to find out the first contact date but not the first contact date after the action date. In tha above example if you see ID1 has an action date on 1/03/2021. The first contact date for ID 1 after action date will be 26/03/2021.  any help on this will be greatly appreciated. Thanks