Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon'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.
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!
Solved! Go to 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]
)
)
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:
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()
)
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]
)
)
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
If you love stickers, then you will definitely want to check out our Community Sticker Challenge!
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
User | Count |
---|---|
144 | |
75 | |
63 | |
51 | |
48 |
User | Count |
---|---|
204 | |
86 | |
62 | |
59 | |
56 |