Need Help to get the total count of phone calls made in first 7 days after the lead is created

[arulmurugavel]

Hi Guys,

I am new to power bi. I am trying to create a report using dynamics 365 data using power query. I have a data like below table 

Lead Name	Lead Created on	Actitvity	Activity created date
Lead 1	28-08-2019	Phone call	29-08-2019
Lead 1	28-08-2019	Phone call	30-08-2019
Lead 1	28-08-2019	Phone call	31-08-2019
Lead 1	28-08-2019	Phone call	01-09-2019
Lead 1	28-08-2019	Phone call	07-09-2019
Lead 1	28-08-2019	Phone call	08-09-2019
lead 2	03-09-2019	Phone call	03-09-2019
lead 2	03-09-2019	Phone call	11-09-2019
lead 2	03-09-2019	Phone call	12-09-2019
lead 3	25-10-2019	Phone call	25-10-2019
lead 3	25-10-2019	Phone call	25-10-2019
lead 3	25-10-2019	Phone call	25-10-2019
lead 3	25-10-2019	Phone call	25-10-2019
lead 4	23-11-2019	Phone call	04-12-2019
lead 4	23-11-2019	Phone call	05-12-2019
lead 4	23-11-2019	Phone call	06-12-2019
lead 4	23-11-2019	Phone call	07-12-2019
lead 5	24-11-2019	Phone call	24-11-2019

I need to get the total count of phone calls made in first 7 days after the lead is created. Like below

Total calls made in the first 7 days of lead created date
Lead name	Total calls in the first 7 days
Lead 1	4
lead 2	1
lead 3	4
Lead 4	Blank or 0
Lead 5	1

Please help me with a solution

[rainer1 · Accepted solution]

Hi,

this is very easy to solve...

this should help you by generate a new calculated column

Calc = 

var leadCreated7d =  Tabelle1[Lead Created on] + 7

Return
IF(Tabelle1[Activity created date] <= leadCreated7d; 1; 0)

Just setting the value to sum.

Regards

Rainer1

[arulmurugavel]

Hello @rainer1 ,

This works... Thanks a lot for your help😀

Regards,

Arul

[rainer1]

You are welcome 😁