The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now
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
)
Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.
User | Count |
---|---|
158 | |
106 | |
96 | |
83 | |
75 |
User | Count |
---|---|
153 | |
137 | |
131 | |
81 | |
61 |