Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Join us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.

Reply
Katerina_SL
Helper II
Helper II

RankNumber depends on client and timedif

Hello everyone!

 

Could you please help me with this issue:

 

I have table in power bi which have columns:
id,
client_id,
order_num,
created_time of order.

I need to create column in this table with rank numbers.
Rank will depends on client_id and created_time of order. But if between created_time more than 12 months. rank will be again start from 1.
For example,
I have client_id 35 with orders 100 (01.09.2020), 102 (10.11.2020), 110 (15.12.2021), 150 (20.12.2021).
Order 100 will have rank number 1, 102 - 2, 110 - 1 (again, because it's more than 12 months between 102 and 110 orders), 150 - 2.
The same for each client_id in this table.

 

I would be very appreciate for any ideas and your help.

4 REPLIES 4
ryan_mayu
Super User
Super User

@Katerina_SL 

could you pls provide some sample data and expected output?





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Katerina_SL_0-1700724607524.png

Here the example. Green column is the results

@Katerina_SL 

you can try to create two columns

Column = 
VAR _last=maxx(FILTER('Table','Table'[Client_ID]=EARLIER('Table'[Client_ID])&&'Table'[Created]<EARLIER('Table'[Created])),'Table'[Created])
return if(ISBLANK(_last) || _last<EDATE('Table'[Created],-12),1,0)

Column 2 = 
VAR _last=maxx(FILTER('Table','Table'[Client_ID]=EARLIER('Table'[Client_ID])&&'Table'[Created]<EARLIER('Table'[Created])&&'Table'[Column]=1),'Table'[Created])
return if('Table'[Column]=1,1,CALCULATE(COUNTROWS('Table'),FILTER('Table','Table'[Client_ID]=EARLIER('Table'[Client_ID])&&'Table'[Created]>=_last&&'Table'[Created]<=EARLIER('Table'[Created]))))

11.PNG

pls see the attachment below





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Thank you for reply. But could you please somehow optimize this function because I have error that there if no enough memory to complete operation.

Helpful resources

Announcements
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.