Rank dublicates in specific time period

[Anonymous]

Hello, need help
I'm trying to rank phone calls for customer by time received and in specific time period.
For example:
I need to rank phone calls from same customer in 14 days from the last call.
First call was in 2.12.19 the second in 6.12.19 the third in 11.12.19, fourth 29.12.19 and the last one in 6.01.20
The expected output should be:
1. 2.12.19
2. 6.12.19
3. 11.12.19
1. 29.12.19 ( cause past more then 14 days from the last call in 11.12.19)
2. 6.01.20

Thanks

[v-jayw-msft]

Hi @Anonymous ,

You can use DATEDIFF() function to defined periods. Please refer to the formulas below and check if the result achieve your expectation.

period = INT(DATEDIFF('Table'[date],CALCULATE(MAX('Table'[date]),FILTER('Table','Table'[customer]= EARLIER('Table'[customer]))),DAY)/14)
rankx = Rankx(Filter('Table', [customer]=earlier ([customer])&&'Table'[period]=EARLIER('Table'[period])), [date], , desc)

Result would be shown as below.

pbix as attached.

Best Regards,

Jay

Community Support Team _ Jay Wang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Thanks, almost there

I'll try to axplane the problem througt expected outcome

table below (new period for anothe 14 days from previous call

customer date PreviousCALL period rankx

A	29/10/2019		0	1
A	03/11/2019	29/10/2019	0	2
A	26/11/2019	03/11/2019	1	1
A	01/12/2019	26/11/2019	1	2
A	10/12/2019	01/12/2019	1	3
A	25/12/2019	10/12/2019	2	1
A	02/01/2020	25/12/2019	2	2
A	07/01/2020	02/01/2020	2	3

[v-jayw-msft · Accepted solution]

Hi @Anonymous ,

Please create an index column in Query Editor and refer to the formulas as below.

tmp = 
var a = CALCULATE(MAX('Table'[date]),FILTER('Table','Table'[customer]= EARLIER('Table'[customer])&&'Table'[Index]=EARLIER('Table'[Index])-1))
var lastcall = IF(ISBLANK(a),'Table'[date],a)
var tmp = INT(DATEDIFF('Table'[date],lastcall,DAY)/14)
return
tmp

period = 
var period = CALCULATE(SUM('Table'[tmp]),FILTER('Table','Table'[customer] = EARLIER('Table'[customer])&&'Table'[Index] <= EARLIER('Table'[Index])))
return
period

rankx = Rankx(Filter('Table', [customer]=earlier ([customer])&&'Table'[period]=EARLIER('Table'[period])), [date], , desc)

 Result would be shown as below.

Pbix as attached.

Best Regards,

Jay

Community Support Team _ Jay Wang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[littlemojopuppy]

This is not going to be syntactically perfect (especially since I don't know the names of your tables), but should give you enough to work with.  This assumes there is a slicer for customers.  If call dates need to be within a certain range you can add criteria to the CALCULATETABLE statement to accommodate that.  Hope this helps!

RANKX(
	CALCULATETABLE(
		[Phone Calls],
		[Phone Calls].Customer = SELECTEDVALUE(Customer)
	),
	[Phone Calls].CallDate,
	TRUE
)

[Anonymous]

Thanks for response
I problem is to rank calls in cycle of 14 days . This what I have to rank all calls
Rankx(Filter(tablename, [customers]=earlier ([customer]) && [callDate]<earlier ([callDate]), [callDate], , desc)

I need to add one more rule than rank call in cycle of 14 days from last callDate.