Need to find if I recieved a phone call after sending voice mail from Dynamics 365 Data source

[arulmurugavel]

Hi People,

I am still new to Power BI. I need to create a report using Dynamics 365 data source.

I am having the below columns in an entity (let’s say Phone call).

Opportunity	Outcome	Created on	Direction	Subject
ABC	Voice mail	12-01-2020	Outgoing	see notes
ABC	Call connected	14-01-2020	incoming	Client called back
ABC	Voice mail	13-01-2019	Outgoing	Followup
ABC	Call connected	14-01-2019	Outgoing	Follow call
EFG	Voice mail	25-12-2019	Outgoing	abcder
EFG	Call connected	12-01-2020	incoming	asfdfd
EFG	Call connected	12-01-2020	Outgoing	xcc
EFG	Call connected	13-01-2020	Outgoing	cdd
EFG	Voice mail	10-01-2020	Outgoing	dfdf
EFG	Call connected	11-01-2020	incoming	dfdf
EFG	Voice mail	10-01-2020	Outgoing	asd
GHI	Voice mail	29-09-2019	Outgoing	dfdf
GHI	Call connected	01-01-2020	incoming	dsfdsf
GHI	Voice mail	10-01-2020	Outgoing	afaf
GHI	Call connected	10-01-2020	incoming	asaf
JKL	Voice mail	31-12-2019	Outgoing	afasf
JKL	Call connected	01-01-2020	incoming	afd
JKL	Call connected	02-01-2020	Outgoing	afdfd

For each opportunity, I will have ‘n’ number of phone call activities. We will record ‘n’ number of voice mail outcome for the same opportunity. I need to find out if I received an incoming call after sending every voice mail for same opportunity. I need to view the reports as below

Opportunity	Voice mail created on	Call received	if received call back?
ABC	12-01-2020	14-01-2020	yes
	13-01-2019		no
EFG	25-12-2019	12-01-2020	yes
	10-01-2020	11-01-2020	yes
	10-01-2020		no
GHI	29-09-2019	01-01-2020	yes
	10-01-2020	10-01-2020	yes
JKL	31-12-2019		no
	31-12-2019	01-01-2020	yes

and 

Opportunity	Count of incoming calls
ABC	1
EFG	2
GHI	2
JKL	1

Thanks is advance,

Regards,

@arulmurugavel 

[amitchandak]

Please refer

https://www.dropbox.com/s/t1emrwofubk3xt2/voicecall.pbix?dl=0

There is for EFG - 25-Dec, Because there is call on 10th Jan

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
https://community.powerbi.com/t5/Community-Blog/Winner-Topper-on-Map-How-to-Color-States-on-a-Map-wi...
https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...
https://community.powerbi.com/t5/Community-Blog/Power-BI-Working-with-Non-Standard-Time-Periods/ba-p...
https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601

[arulmurugavel]

Hi @amitchandak ,

Thanks for your support.

The file which you have shared is not opening in my Power BI desktop. It is showing the below error.

“The queries were authored with a newer version of Power BI desktop and might not work with your version”

After closing this error the file opened, but I think some part is missing. Need help

Regards,

Arul

[amitchandak · Accepted solution]

The date was taken as varchar - converted to date --- If the date is detected correctly no need

datein = mid(voicecall[Created on],4,2) &"/"& left(voicecall[Created on],2) & "/" & right(voicecall[Created on],4)
Column 1 = minx(FILTER(voicecall,voicecall[Opportunity]=EARLIER(voicecall[Opportunity]) && voicecall[datein]>=EARLIER(voicecall[datein]) && voicecall[Outcome]="Call connected" && EARLIER(voicecall[Outcome])<>"Call connected" && voicecall[Direction]="incoming" ),voicecall[datein])
Column 2 = minx(FILTER(voicecall,voicecall[Opportunity]=EARLIER(voicecall[Opportunity]) && voicecall[datein]>EARLIER(voicecall[datein]) && voicecall[Outcome]="Voice mail" && voicecall[Direction]="Outgoing" ),voicecall[datein])
Column 3 = if((ISBLANK(voicecall[Column 2]) || voicecall[Column 2]> voicecall[Column 1]) && not(ISBLANK(voicecall[Column 1])),"Yes","No")

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
https://community.powerbi.com/t5/Community-Blog/Winner-Topper-on-Map-How-to-Color-States-on-a-Map-wi...
https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...
https://community.powerbi.com/t5/Community-Blog/Power-BI-Working-with-Non-Standard-Time-Periods/ba-p...
https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601

[arulmurugavel]

Hi @amitchandak ,

It works..Thanks a lot for your support 👍

Regards,

Arul