cancel
Showing results for
Did you mean:
Highlighted
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

Accepted Solutions
Highlighted
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
Highlighted
Resolver II

Use the below RANKX

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

,

ASC

)

Highlighted
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!

Highlighted
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

Highlighted
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!

Highlighted
Helper III

@amitchandak  thanks,

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

Highlighted
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)

Highlighted
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
)

Announcements

#### Power Platform Community Conference

Check out the on demand sessions that are available now!

#### Microsoft Power Platform Communities

Check out the Winners!

#### Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors