How to return the earliest date/time among a subset of data in a table

trcstuart — Mon, 11 Sep 2023 17:00:59 GMT

Hi,

This is my first post on this forum so if it can be improved in any way or in the wrong spot please let me know!

I have a table called 'REQUISITION CYCLE' that I created from another table. The relationships for this table look like this:

The 'REQUISITION CYCLE' table includes a field called 'ACTION_DATE' which lists the dates and times that 'actions' happened to the Requisitions. It also includes a field called 'STATUS' which lists the 'actions' that have occured (such as authorized, reviewed, etc.). What I would like to do is to create a new column called 'FIRST REVIEW' to capture the date/time that each Requisition is first reviewed (Requisitions can be reviewed multiple times).

Here is the DAX I have written so far:

Here is the data it is giving me:

The date being returned in the first review column is the earliest for the document but not the earliest for document, for the 'REVIEWED' status.

Any help would be much appreciated!'

Thanks,

Tyler

Re: How to return the earliest date/time among a subset of data in a table

tamerj1 — Mon, 11 Sep 2023 18:02:39 GMT

Hi trcstuart

please try

First Review =
MINX (
FILTER (
CALCULATETABLE (
'Requisition Cycle',
ALLEXCEPT ( 'Requisition Cycle', 'Requisition Cycle'[Document_ID] )
),
'Requisition Cycle'[Status] = "REVIEWED"
),
'Requisition Cycle'[Action_Date]
)

Re: How to return the earliest date/time among a subset of data in a table

trcstuart — Mon, 11 Sep 2023 20:40:34 GMT

Thanks tamerj1 !! Worked perfectly!

topic How to return the earliest date/time among a subset of data in a table in DAX Commands and Tips

How to return the earliest date/time among a subset of data in a table

Re: How to return the earliest date/time among a subset of data in a table

Re: How to return the earliest date/time among a subset of data in a table