Solved: Help with SORT - Page 2

Martina_Hawkins · 04-06-2020 04:28 AM

Hi,
I am trying to create a slice to show list of Equipment with Expired Calibration tests.
I have a table which will list the
Calibration Request Ref(key),
Item Id,
Date Calibrated
Calibration Due Date (Expiry Date)
etc.
Each time item is calibrated, a new row will be added so always want to check the most recent entry.

I have this expression in the Slice. Getting Error :Sort has invalid inputs.
Is there easier way of creating this list.

IFS(
Month(Today())<>“1”,
AND(
Day([Calibration Due Date])<Day(Today()),
Month([Calibration Due Date])<=Month(Today()),
YEAR(TOP(SORT([Calibration Due Date]),1))<=Year(Today())
),

Month(Today())=“1”,
YEAR(TOP(SORT([Calibration Due Date]),1))<Year(Today())
)

Steve

To get your slice to display only the row for each Item Id with the latest Calibration Due Date, try this as the row filter expression (replace MyTable with the name of the table the slice is built upon):

(
  [_THISROW]
  = MAXROW(
    "MyTable",
    "Calibration Due Date",
    ([_THISROW].[Item Id] = [Item Id])
  )
)

To get your slice to display only the row for each Item Id with the latest Calibration Due Date only if the Calibration Due Date is due, try this:

(
  TODAY()
  >= MAX(
    SELECT(
      MyTable[Calibration Due Date],
      ([_THISROW].[Item Id] = [Item Id])
    )
  )
)

View solution in original post