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.
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 |
Solved! Go to Solution.
Hi, @uc
Thank you for your feedback.
Please try the below.
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
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
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 ()
)
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
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
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
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
Hi, @uc
Thank you for your feedback.
Please try the below.
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
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
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
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 ()
)
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
It works Kim 🙂. Thanks a lot!!
It works! Thanks a lot Kim 🙂. Timely help is much appreciated.
Quick thought
First contact =
var lookupID=yourtable[id]
Calculate(min(contact date),filter(your table, [ID] =lookupid
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
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 |
---|---|
112 | |
97 | |
84 | |
67 | |
61 |
User | Count |
---|---|
150 | |
120 | |
99 | |
87 | |
68 |