Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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=
Solved! Go to Solution.
@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)
@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
@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.
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)
Use the below RANKX
RANKX(
‘Table’,
‘Table'[time between]
,
ASC
)
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
99 | |
75 | |
73 | |
49 |
User | Count |
---|---|
146 | |
108 | |
106 | |
90 | |
62 |