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

Accepted Solutions
Highlighted

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

Use the below RANKX

 

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

,

ASC

)

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

Highlighted

@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

Highlighted

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

Highlighted

@amitchandak  thanks,

Please find the pbix file in the below link:

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

Highlighted

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

Highlighted

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

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

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