rank with conditions

[Anonymous]

Hi,

I want to rank the Customer_ID based on the date column with the condition that if the date is longer than 61 days ago, the rank will start from 1 again as seen in below table:

Customer_ID	date	rank
aaa	2020/2/1	1
aaa	2020/2/1	1
aaa	2020/2/3	2
aaa	2020/3/1	3
aaa	2020/7/1	1
aaa	2020/7/1	1
aaa	2020/7/2	2

I created two calculated columns, last date and the date between the dates:

Customer_ID	date	last date	time between
aaa	2020/2/1		0
aaa	2020/2/1		0
aaa	2020/2/3	2020/2/1	2
aaa	2020/3/1	2020/2/3	25
aaa	2020/7/1	2020/3/1	122
aaa	2020/7/1	2020/3/1	122
aaa	2020/7/2	2020/7/1	1

How can I calculate the rank?

I tried 

rank= 

IF(

[time between] = 0 || [time between] > 61, 1,

RANKX (

FILTER (

'table'

,'table'[Customer_ID] = EARLIER ( [Customer_ID] )

&& [shop_id] = EARLIER([shop_id])

&& [date] < EARLIER([date])

)

,'table'[date]

,

,ASC

,Dense

)

But this doen't give me the correct result. Please advise

[amitchandak]

@Anonymous , All new columns

You can have few of them as var in a column if needed

last date= maxx(filter(Table,[customer] = earlier([customer])),[date])

Diff = datediff([date],[last date],day)
61 Flag = quotient([Diff],61)

Rank = RANKX (
FILTER (
'table'
,'table'[Customer_ID] = EARLIER ( [Customer_ID] )
&& [shop_id] = EARLIER([shop_id]) && [61 Flag] = EARLIER([61 Flag])
), [date],,asc, dense)

[Anonymous]

@amitchandak thanks for your reply.

your DAX function gives me the same result as the one I tried before.

condition: [time between] is greater than 61, then start rank from 1 and next date 2 and so on. 

In this example date: 2020/7/2 should be ranked as 2 not 4

[amitchandak]

@Anonymous , Can remove the shop id and try. I just added as that was there in your script

Rank = RANKX (
FILTER (
'table'
,'table'[Customer_ID] = EARLIER ( [Customer_ID] )
&& [61 Flag] = EARLIER([61 Flag])
), [date],,asc, dense)

Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

[Anonymous]

@amitchandak 

Hi, I think I figured where the issue lies.

I created a flag for 61 days,

-> quotient([time between]), 61)

but this flags only the dates where the [time between] is gretaer than 61 not the dates afterwards.

So when I used the below rank funtion, it ranks all the dates where there is no flag (including the dates after the 61 days flag), 

but I want that after 61 days the rank should start again from 1 and next date afterwards should be 2 and so on

Rank = RANKX (
FILTER (
'table'
,'table'[Customer_ID] = EARLIER ( [Customer_ID] )
&& [61 Flag] = EARLIER([61 Flag])
), [date],,asc, dense)

[Anonymous · Accepted solution]

I was able to solve this issue by adding the following condition:

[date] >= EARLIER([date]) -61

frequency = RANKX (

FILTER (

'table'

,'table'[Customer_ID] = EARLIER ( [Customer_ID] )

&& [date] >= EARLIER([date]) -61

), [date],

,asc

, dense

)

[Anonymous]

@amitchandak  thanks,

Please find the pbix file in the below link:

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

[sreenub]

Use the below RANKX

RANKX(
‘Table’,
‘Table'[time between]

,

ASC

)