Bottom N records with a condition

Question

I want 10 oldest requests based on the received date ascending with a specific condition. I’m not sure how can I apply this on the calculation. I have days pending already calculated in a table and I need to implement to get the 10 oldest pending requests by office and received date.

I have written this calculation

CALCULATE(SUM(table[days_pending]),
                FILTER(ALLEXCEPT(table, table[office]),  
                    (table[Fiscal_Year_Closed] >= MAX(table[Fiscal_Year]) &&
                     table[Fiscal_Year] < MAX(table[Fiscal_Year]))))

but I’m not sure how can I get the 10 oldest received dates along with this condition (table[Fiscal_Year_Closed] MAX(table[Fiscal_Year]) && table[Fiscal_Year] < MAX(table[Fiscal_Year] applied.

Something like to convert this sql statement in powerbi syntax.

select top 10 office, received_date, sum(days_pending) from table where Fiscal_Year_Closed >= '2024' and Fiscal_Year < '2024' group by received_date, office order by received_date asc

How can I go about achieving this? Thanks in advance!

[![enter image description here][1]][1]

Answer 1

Add a new table to your model with the following:
Table Name: Dim OldestPendingRequest

Column	Rank	Column sort
10th Request and Number of Days Pending	10	1
9th	9	2
8th	8	3
7th	7	4
6th	6	5
5th	5	6
4th	4	7
3rd	3	8
2nd	2	9
Oldest Request and Number of Days Pending	1	10

The table doesn't need to have any relationships created. Set the Column to be Sort by... Column sort.

Then create a measure with:

10 Oldest Pending Requests =
  var rowOrdinal = SELECTEDVALUE('Dim OldestPendingRequest'[Rank], 0)
  var preFilter = 
    FILTER(
      'table',  
      'table'[Fiscal_Year_Closed] >= MAX('table'[Fiscal_Year]) && 
      'table'[Fiscal_Year] < MAX('table'[Fiscal_Year])
    )
  var oldestRows = TOPN(rowOrdinal, preFilter, [received_date], ASC)
  var neededRow = TOPN(1, oldestRows, [received_date], DESC)
  var result = CALCULATE(MIN(table[received_date]), neededRow) & UNICHAR(10) & CALCULATE(MIN(table[days_pending]), neededRow)
  RETURN IF(COUNTROWS(neededRow)>0, result)

Now you should be able to create a Matrix with:

• Rows: table[office]
• Columns: 'Dim OldestPendingRequest'[Column]
• Values: [10 Oldest Pending Requests]