RankNumber depends on client and timedif

[Katerina_SL]

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.

[ryan_mayu]

@Katerina_SL 

could you pls provide some sample data and expected output?

[Katerina_SL]

Here the example. Green column is the results

[ryan_mayu]

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

pls see the attachment below

[Katerina_SL]

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.