Return value from another column, same table, multiple criteria, columns have blanks and repeats

[ROKRI]

Greetings All, 

I am trying to populate a new column in a table based on multiple criteria from other columns. LOOKUPVALUE doesn't quite do it because most of my columns have blanks and/or repeats. I have searched and searched the community, and while I have found solutions that are close to my question, I cannot get any of them to work, so I come to you for help please.  

I've added the table below (table name = [Procurement Tracker]) as a point of reference for this description. The end result I'm looking to calculate is the # of days between the PO "Entry Date" and the PR "Entry Date" using the "PR Num" as the identifier for the PO to find the corresponding "PR #". I want to populate the "Calc- PR Entry Date" column (blue) for "Doc Type" = PO using the "PR Num" (green) (multiple repeating values) to match to the "PR #" (light red) (unique values but lots of blanks) to return the PR "Entry Date" (blue) for all POs. Here's a link to the excel file for the image below if helpful, 

For added context:

• My table will have many thousands of values, lots of them having nothing to do with the scenario described above but will have "PR Num" and "Entry Date" values as I display with the three last rows.
• Additionally, with the solution above, I'm hoping to be able to alter it to do similiar calculations such as Entry Date difference between "Doc Type" = PR and "Doc Type" = PR2 or same between PR and NTF, etc. (referencing last 3 rows). 
• I did calculate a new table from the main [Procurement Tracker] one with only PR # (unique value) and Entry Date but still couldn't solve my issue.

I hope I've explained that well enough. I've tried to include all the criteria I've come across as hurdles in getting other solutions to work. Standing by to answer any questions.

Thank you,

Russ

[Ashish_Mathur · Accepted solution]

Hi,

Write these calculated column formulas

PR entry date = if(Data[Doc Type]="PO",CALCULATE(MAX(Data[Entry Date]),FILTER(Data,Data[PR #]=EARLIER(Data[PR Num])&&Data[Entry Date]<EARLIER(Data[Entry Date]))),BLANK())

Diff = if(ISBLANK(Data[PR entry date]),BLANK(),1*(Data[Entry Date]-Data[PR entry date]))

Hope this helps.

[ROKRI]

@Ash Thank you for this solution!! It works great. I made one adjustment changing the last "<EARLIER" to "<=EARLIER" to capture PRs and POs entered on the same day. I didn't include the portion to calculate the difference in dates yet as I need to add multiple nested if/then forumlas to pass the same argument each for Doc Type = "NTF", ="PR 2", and ="Proc Waiver" in addtion to ="Proc Summary".  

@DataInsights I couldn't get your proposed solution to return a value unfortunately. However, setting variables is a great idea because of the long nested if/then. I tried setting the variables within Ashish's formula but couldn't figure it out. Hoping you may be able to advise.

Below is the formula that is working evaluting PR Entry Date for both "PO Summary" and "NTF". I've made the PO Summary portion green and the NTF portion blue for ease of reference. I'm also adding an image of the table with the returned values. I haven't added the remaining doc types yet in hopes of being able to set variables. Note the column names are adjusted slightly from the original example as I'm back in PBI now working the formulas. I appreciate any recommendations you have to simply the formula by setting variables, which have huge potential to simply the formula and maximize functionality. I appreciate any continued guidance you can offer. 

PR Entry Date =

if('Procurement Tracker for Dynamic View'[Document Type]="PO Summary",calculate(max('Procurement Tracker for Dynamic View'[Entry Date]), filter('Procurement Tracker for Dynamic View','Procurement Tracker for Dynamic View'[PR #]=EARLIER('Procurement Tracker for Dynamic View'[PR Number])

&&'Procurement Tracker for Dynamic View'[Entry Date]<=EARLIER('Procurement Tracker for Dynamic View'[Entry Date]))),if('Procurement Tracker for Dynamic View'[Document Type]="NTF",calculate(max('Procurement Tracker for Dynamic View'[Entry Date]),

filter('Procurement Tracker for Dynamic View','Procurement Tracker for Dynamic View'[PR #]=EARLIER('Procurement Tracker for Dynamic View'[PR Number]) &&'Procurement Tracker for Dynamic View'[Entry Date]<=EARLIER('Procurement Tracker for Dynamic View'[Entry Date]))),Blank()))

Thank you both!!

Russ

[Ashish_Mathur]

You are welcome.

[DataInsights]

@ROKRI,

Try this calculated column:

Days between PR Entry and PO Entry =
VAR vPRNum = 'Procurement Tracker'[PR Num]
VAR vPRRow =
    FILTER (
        'Procurement Tracker',
        'Procurement Tracker'[Doc Type] = "PR"
            && 'Procurement Tracker'[PR Num] = vPRNum
    )
VAR vPREntryDate =
    MAXX ( vPRRow, 'Procurement Tracker'[Entry Date] )
VAR vDateDiff =
    DATEDIFF ( vPREntryDate, 'Procurement Tracker'[Entry Date], DAY )
VAR vResult =
    IF ( 'Procurement Tracker'[Doc Type] = "PO", vDateDiff )
RETURN
    vResult