cancel
Showing results for
Did you mean:
Helper III

## rank with conditions

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
)

1 ACCEPTED SOLUTION
Helper III
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
)
7 REPLIES 7
Super User IV

@nabe , 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)

Proud to be a Super User!

Helper III

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

Super User IV

@nabe , 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.

Proud to be a Super User!

Helper III

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)

Helper III
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
)
Helper III

@amitchandak  thanks,

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

Resolver II

Use the below RANKX

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

,

ASC

)

Announcements

#### Happy New Year from Power BI

This is a must watch for a message from Power BI!