Forum Discussion

Katerina_SL's avatar
Katerina_SL
Icon for Helper II rankHelper II
2 years ago

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.

Need Help

4 Replies

      • ryan_mayu's avatar
        ryan_mayu
        Icon for Super User rankSuper User
        2 years ago

        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