Check to see if CallingNumber exists within the last 14 days

[BALKRAJ]

Hi

I have a table with call data like below :

CallingNumber, DateTime, AgentID, Answered

87654321, 2017-02-16 21:01:45, 1258, TRUE

12345678, 2017-02-01 22:25:58, 1258, TRUE

87456123, 2017-02-01 14:26:01, 1256, FALSE

12345678, 2017-01-25 17:32:18, 1247, TRUE

I need to create a new column where I check to see if a [CallingNumber] has not called within the last 14 days before current records [DateTime] and [Answered] = TRUE

If above criterias is true then the new column returns TRUE

So above will return following table after new column added:

CallingNumber, DateTime, AgentID, Answered, FirstCall

87654321, 2017-02-16 21:01:45, 1258, TRUE, TRUE

12345678, 2017-02-01 22:25:58, 1258, TRUE, FALSE

87456123, 2017-02-01 14:26:01, 1256, FALSE, FALSE

12345678, 2017-01-25 17:32:18, 1247, TRUE, TRUE

This is all done in the same table if possible.

I hope above makes sence and that you can help me.

Thanks in advance

[CheenuSing]

Hi @BALKRAJ

I have found a solution. To verify I need more data .

Can you post more data on oneDrive / DropBox and send me the link

Cheers

CheenuSing 

[BALKRAJ]

Hi @CheenuSing

Have you had a chance to look at the data I uploaded?

Thanks in advance

[CheenuSing]

Hi @BALKRAJ

I looked at your data.  My observations

a) There are calling numbers that are not numbers. Like anonymous, NULL. Can these rows be filtered.

b) The Date column includes time and for some numbers there are two records. The difference between the records is     in minutes and not days.  How do you want to handle such records

Look forward to hearing from you.

Cheers

CheenuSing 

[BALKRAJ]

Hi again  @CheenuSing

I'm still very green at Power BI and I don't know if it got anything to say, but I need to be able to slice on agent - so I think that means that we need to have it as a measure, right?

And can that be done?

[BALKRAJ]

Hi @CheenuSing

a) We are not able to filter these records as we need those data in other measures - however we do not need these records in this scenario.

b) The difference in minutes should be handle same way as if there were days between them - the call has not been handled with success og therefor the number is making a new call. Did you remove those records that are Answered = False()?

Thank you

[CheenuSing]

Hi @BALKRAJ

Please check the file uploaded in One drive

https://1drv.ms/u/s!ApP3mBZyGaHfzxrSzgub6q7So4Ag

Let me know if this is what you wanted.

Cheers

CheenuSing

[BALKRAJ]

Hi @CheenuSing

Thanks for the solution, however I think we might have misunderstood each other.

I've looked at the formulas and I'm not sure if it is the right way to do it - in PrevDate you look for the very first call ever made on a specific calling number, correct?

PrevDate = CALCULATE(MIN([Date]);FILTER(RawDataSource;[CallingNumber]=EARLIER(RawDataSource[CallingNumber]) ))

Lets say I have 7 records with same calling number like below and all of them answered :

Calling number | Date | Answered

9544220125 | 2017-02-20 16:00:00 | 1 (This is not a first call) (PrevDate = 2017-02-15 16:00:00)

9544220125 | 2017-02-15 16:00:00 | 1 (This is not a first call) (PrevDate = 2017-02-10 16:00:00)

9544220125 | 2017-02-10 16:00:00 | 1 (This is a first call) (PrevDate = 2017-01-09 16:00:00)

9544220125 | 2017-01-09 16:00:00 | 1 (This is not a first call) (PrevDate = 2017-01-07 16:00:00)

9544220125 | 2017-01-07 16:00:00 | 1 (This is a first call) (PrevDate = 2017-12-20 16:00:00)

9544220125 | 2016-12-20 16:00:00 | 1 (This is a first call) (PrevDate = 2016-12-05 16:00:00)

9544220125 | 2016-12-05 16:00:00 | 1 (This is a first call) (PrevDate = no date)

With the above sample data and the formula you provided I'll get PrevDate = 2016-12-05 16:00:00 on all records right?

The correct PrevDate needs to be the closest earlier record to current record ... makes sense? 

Thanks

[parry2k]

Whenever there is a solution to this, I woud like to see it. Thanks in advance!

[BALKRAJ]

Hi @CheenuSing

Please see sample data in excel format at https://1drv.ms/x/s!ApWO17TJv3pvgaEMDGheML8uQ-fLEw

There are a few different days of data and some of them should return TRUE