Help with SORT

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())
)

So… do you want to show in a sliced view only Equipment’s last entry… meaning all Equipmets are shown only once?

yes, so if a piece of equipment has expiry date 5/4/19 and then 5/4/20, I only want to check the latest and not show 5/4/19

For example… MAXROW(“TableName”,“Date”,[Equipment]=[_THISROW].[Equipment])=[KeyColumn]

1 Like

not sure where this fits into my expression above where I need to check the Calibration Due Date.

From list below I want to pull out only Calibration Ref 3 as its the only one Due (Expired)

Calibration Ref No Item ID Calibration Date Calibration Due Date
1 CATA-008 05/03/2020 05/03/2021
2 ACC-0001 03/04/2018 03/04/2019
3 ACC-0001 03/04/2019 03/04/2020
5 TEST-002 10/03/2020 11/03/2021

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])
    )
  )
)
3 Likes

From attached view I don’t want to show the first row with Expiry date 3/4/2019

Thank you so much @Steve. That’s exactly what I needed. Your help is much appreciated :grinning:

1 Like

@Steve I need help with 1 more thing please.

I want to send an email notification when a Calibration has expired.
So I added a column Calibration Expired which I want to have either Expired or Not Expired (initial Value) and will be hidden. Shown for now so I can see value.

When this field changes it will trigger the workflow to send the email notification.
I thought I could use your formula above in the column App formula but I can’t get it to work.
It also seems to change back to a yes/no type from Text type. Even when it is Text type the value is False - see attachment,

Probably even better to have Expiring, using today -7 to notify of calibrations expiring in next 7 days.

Also, would an App formula constantly keep the Expired field updated or is an App formula only triggered when Calibration Due Date is first entered?

In your expression, replace this:

[Calibration Expired]="Expired"

with just this:

"Expired"

In your expression, replace this:

TODAY()

with this:

(TODAY() - 7)

An app formula of a normal (non-virtual) column is updated only when the row is updated, such as when edited in a form or by an action, and the result is saved to the spreadsheet. An app formula of a virtual column is updated each time the app syncs, and is not saved to the spreadsheet.

Thanks again for your help @Steve

1 Like