Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Don't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.

Reply
martipe1
Helper II
Helper II

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

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

IDNumberEntry DateRequisition
RH11Jan 13, 2025 
RZ12Jan 13, 2025 
PO1Jan 15, 202512
RH13Jan 17, 2025 
RZ14Jan 17, 2025 
RH15Jan 17, 2025 
PO2Jan 16, 2025 
PO3Jan 17, 2025 
PO4Jan 18, 202514

 

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:

 

IDNumberEntry DateRequisitionRequisition Date
RH11Jan 13, 2025  
RZ12Jan 13, 2025  
PO1Jan 15, 202512Jan 13, 2025
RH13Jan 17, 2025  
RZ14Jan 17, 2025  
RH15Jan 17, 2025  
PO2Jan 16, 2025  
PO3Jan 17, 2025  
PO4Jan 18, 202514Jan 17, 2025

 

Any ideas?

 

Thank you in advance for your help!

1 ACCEPTED SOLUTION
Irwan
Super User
Super User

hello @martipe1 

 

please check if this accomodate your need.

Irwan_0-1737592779487.png

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.

View solution in original post

4 REPLIES 4
anmolmalviya05
Super User
Super User

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



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
Super User
Super User

@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
Super User
Super User

hello @martipe1 

 

please check if this accomodate your need.

Irwan_0-1737592779487.png

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.

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.

Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan25PBI_Carousel

Power BI Monthly Update - January 2025

Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.