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.

     

DataInsights's avatar
DataInsights
Super User
2 years ago

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