cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Helper III
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
aaa2020/2/1  1
aaa2020/2/1  1
aaa2020/2/3  2
aaa2020/3/1  3
aaa2020/7/1  1
aaa2020/7/1  1
aaa2020/7/2  2

 

I created two calculated columns, last date and the date between the dates:

 

Customer_ID    date  last date  time between
aaa2020/2/1      0
aaa2020/2/1      0
aaa2020/2/3   2020/2/1    2
aaa2020/3/1   2020/2/3    25
aaa2020/7/1   2020/3/1    122
aaa2020/7/1  2020/3/1    122
aaa2020/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
)
 
But this doen't give me the correct result. Please advise

 

1 ACCEPTED SOLUTION

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
)

View solution in original post

7 REPLIES 7
Super User IV
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)



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

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

キャプチャ.PNG

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



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

@amitchandak 

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)

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
)

View solution in original post

@amitchandak  thanks,

Please find the pbix file in the below link:

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

Resolver II
Resolver II

Use the below RANKX

 

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

,

ASC

)

Helpful resources

Announcements
secondImage

Happy New Year from Power BI

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

December Update

Check it Out!

Click here to read more about the December 2020 Updates!

Community Blog

Check it Out!

Click here to read the latest blog and learn more about contributing to the Power BI blog!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors