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 ta...
Need Help
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

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

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.