Create a new column with the result of looking for a value in the same table

[martipe1]

I have a table that has requisitions and Purchase Orders (POs)

ID	Number	Entry Date	Requisition
RH	11	Jan 13, 2025
RZ	12	Jan 13, 2025
PO	1	Jan 15, 2025	12
RH	13	Jan 17, 2025
RZ	14	Jan 17, 2025
RH	15	Jan 17, 2025
PO	2	Jan 16, 2025
PO	3	Jan 17, 2025
PO	4	Jan 18, 2025	14

When a requisition is converted into a PO the ID changes from RH to RZ and the PO saves the requisition number in the column text requisition.
The number column is the PO or Requisition number

Not all POs need a requisition to be created, that's the reason you don't see all POs with a requisition value
There might be the case when an order has the same number that one requisition created earlier or later than the PO.

What I want to achieve is to create a new column where if it's a PO and it has requisition value, retrieve the entry date for that requisition as the example below:

ID	Number	Entry Date	Requisition	Requisition Date
RH	11	Jan 13, 2025
RZ	12	Jan 13, 2025
PO	1	Jan 15, 2025	12	Jan 13, 2025
RH	13	Jan 17, 2025
RZ	14	Jan 17, 2025
RH	15	Jan 17, 2025
PO	2	Jan 16, 2025
PO	3	Jan 17, 2025
PO	4	Jan 18, 2025	14	Jan 17, 2025

Any ideas?

Thank you in advance for your help!

[anmolmalviya05]

Hi @martipe1, Please try the below measure:

Requisition Date =
IF(
'Table'[ID] = "PO" && NOT(ISBLANK('Table'[Requisition])),
CALCULATE(
MAX('Table'[Entry Date]),
FILTER(
'Table',
'Table'[Number] = 'Table'[Requisition] &&
'Table'[ID] <> "PO"
)
),
BLANK()
)

[martipe1]

Thank you very much for your answer.

The requisition column is text I had to do some changes to your suggestion but don't know why it's not working

This is my measure:

ReqDate = IF (

    LEFT(Table[ID],1) = "P" && NOT (ISBLANK(

if(ISERROR(VALUE(Table[Requisition])), BLANK(), VALUE(Table[Requisition]))

    )),

        CALCULATE(

    MAX(Table[EntryDate]),

    FILTER(

    Table, Table[Number] = if(ISERROR(VALUE(Table[Requisition])), 0, VALUE(Table[Requisition])) && LEFT(Table[ID],1) <> "P"

    )

    ),

    BLANK()

    )

The result is all blanks

Thanks in advance for your help

[Kedar_Pande]

@martipe1 

Create new column:
Requisition Date =
IF(
Data[ID] = "PO" && NOT ISBLANK(Data[Requisition]),
CALCULATE(
MAX(Data[Entry Date]),
FILTER(
Data,
Data[Number] = EARLIER(Data[Requisition]) && LEFT(Data[ID], 2) = "RH"
)
),
BLANK()
)

💡 If this helped, please give Kudos 👍 or mark it as a Solution ✅.
Best regards,
Kedar
🌐 Connect on LinkedIn

[Irwan · Accepted solution]

hello @martipe1 

please check if this accomodate your need.

create a new calculated column with following DAX.

Requisition Date = 
IF(
    not ISBLANK('Table'[Requisition]),
    MAXX(
        FILTER(
            'Table',
            'Table'[Entry Date]<EARLIER('Table'[Entry Date])
        ),
        'Table'[Entry Date]
    )
)

Hope this will help.

Thank you.