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:
I created two calculated columns, last date and the date between the dates:
How can I calculate the rank?
Go to Solution.
View solution in original post
Use the below RANKX
@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!
@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
@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.
Please find the pbix file in the below link:
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
Check out the on demand sessions that are available now!
Check out the Winners!
Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.