topic Re: Ranking by Date with Grouping in DAX Commands and Tips

Ranking by Date with Grouping

Anonymous — Wed, 18 Jan 2023 20:14:13 GMT

Hello All,

I am new to PowerBI and have looked at a lot of articles similar to this, but have yet to find a solution that works for me.

I have a table that has purchase order numbers with their respective order dates as well as item codes that were on that purchase order.

I am trying to add a "ranking" column that will rank each row idividually by the date grouped by the item code & PO. So for example data like this:

PO #	PO Date	Item Code
5462	7/8/2022	58-1234
5462	9/10/2021	58-1234
5462	8/10/2022	58-1234
5462	4/20/2021	19-576
7890	12/20/2022	58-1234
7890	9/20/2022	58-1234
2323	7/14/2021	19-576
2323	9/12/2022	58-1234
2323	7/6/2020	19-576
4567	1/1/2023	58-1234

Turning into this:

PO #	PO Date	Item Code	Rank
5462	4/20/2021	19-576	1
5462	9/10/2021	58-1234	3
5462	7/8/2022	58-1234	2
5462	8/10/2022	58-1234	1
7890	9/20/2022	58-1234	2
7890	12/20/2022	58-1234	1
2323	7/6/2020	19-576	2
2323	7/14/2021	19-576	1
2323	9/12/2022	58-1234	1
4567	1/1/2023	58-1234	1

Essentially even if there is over 50+ entires for each PO# & part #, I want the 1,2, & 3 rankings to be the most recent dates/entries.

Does anyone know of a simple way to do this?

Re: Ranking by Date with Grouping

CNENFRNL — Wed, 18 Jan 2023 21:04:11 GMT

For fun only, a showcase of powerful Excel formula,

Re: Ranking by Date with Grouping

Anonymous — Wed, 18 Jan 2023 21:27:09 GMT

So this doesnt give me an error or anything, but its still not exactly what I am needing. Once entering this it is just giving me a rank column with a 1 in every row in the table. I need it to rank by purchaseorderdate top 3 newest to oldest and grouping by ItemCode. Here is an image of my visual showing you what im seeing.

To add more context, the reason I am doing this is because our order people want to see the three most recent POs for each item code. Here is an example of what the finalized report looks like. Youll see the PO#s are different and the date is sorted from most recent to oldest (top 3 only).

This isnt the full page, at the top it just shows the item code along with our vendor.

Thank you for your response!

Re: Ranking by Date with Grouping

Anonymous — Thu, 19 Jan 2023 00:12:01 GMT

hello Anonymous ,
create anew calculated column,

RANK = RANKX(fILTER('Table','Table'[PO #]=EARLIER('Table'[PO #])),'Table'[PO Date],,ASC,dENSE)

Re: Ranking by Date with Grouping

Anonymous — Thu, 19 Jan 2023 05:43:23 GMT

Hi Anonymous ,

Please try below steps:

1. below is my test table

Table:

2. create a measure with below dax formula

Measure = VAR cur_po = SELECTEDVALUE ( 'Table'[PO #] ) VAR cur_item_code = SELECTEDVALUE ( 'Table'[Item Code] ) VAR tmp = FILTER ( ALL ( 'Table' ), 'Table'[Item Code] = cur_item_code && 'Table'[PO #] = cur_po ) RETURN RANKX ( tmp, CALCULATE ( SELECTEDVALUE ( 'Table'[PO Date] ) ),, DESC, DENSE )

3. add a table visual with fields and measure

Please refer the attached .pbix file.

Best regards,
Community Support Team_Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.