Power BI is turning 10, and we’re marking the occasion with a special community challenge. Use your creativity to tell a story, uncover trends, or highlight something unexpected.
Get startedJoin 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.
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.
could you pls provide some sample data and expected output?
Proud to be a Super User!
Here the example. Green column is the results
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
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.
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
56 | |
54 | |
54 | |
37 | |
29 |
User | Count |
---|---|
78 | |
64 | |
45 | |
40 | |
40 |