Forum Discussion

ROKRI's avatar
ROKRI
Helper I
2 years ago
Solved

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

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. ...
Need Help
  • Ashish_Mathur's avatar
    Ashish_Mathur
    2 years ago

    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's avatar
ROKRI
Helper I
2 years ago

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's avatar
Ashish_Mathur
Super User
2 years ago

You are welcome.