Forum Discussion

rachaelwalker's avatar
rachaelwalker
Resolver III
4 years ago
Solved

Pulling Dates from another table with IF statement

I need help with a formula. I have two tables. My Quote table needs to pull the Expected_Close_Date from my Opportunity table based on matching Opportunity IDs. It has a one-to-many relationship because quotes have multiple products that go under the same opp. I need to pull the Expected_Close_Date for all items on the quote when OppID = OppID

 

Opportunity Table (1)

OppID

Expected_Close_Date

 

Quote Table (many)

QuoteID

ProductID

OppID

 

This will at least get me started, but I am also finding some have blank dates. If the Expected_Close_Date is blank, how do I tell it to populate a native date field that is already in the Quote table. Thank you!

 

Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi rachaelwalker ,

    How about something like:

    Expected_Close_Date (in Quote Table) =
VAR _Expected_Close_Date =
    LOOKUPVALUE (
        'Opportunity Table'[Expected_Close_Date],
        'Opportunity Table'[OppID], 'Quote Table'[OppID]
    )
VAR _Result =
    IF (
        ISBLANK ( _Expected_Close_Date ),
        'Opportunity Table'[insert some date field here],
        _Expected_Close_Date
    )
RETURN
    _Result



4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi rachaelwalker ,

    How about something like:

    Expected_Close_Date (in Quote Table) =
VAR _Expected_Close_Date =
    LOOKUPVALUE (
        'Opportunity Table'[Expected_Close_Date],
        'Opportunity Table'[OppID], 'Quote Table'[OppID]
    )
VAR _Result =
    IF (
        ISBLANK ( _Expected_Close_Date ),
        'Opportunity Table'[insert some date field here],
        _Expected_Close_Date
    )
RETURN
    _Result



    • SUMESHKUMAR22's avatar
      SUMESHKUMAR22
      Helper IV
      4 years ago

      Hi Anonymous , KNP ,
      In the above problem statement if the relationship has been many-many & I need to pull the text column (e.g. Person name) instead of Expected close date then what dax approach should we take?

      Thaks in Advance,

  • KNP's avatar
    KNP
    Super User
    4 years ago

    If you want a Power Query solution...

     

    Opportunity

    let
  Source = Table.FromRows(
    Json.Document(
      Binary.Decompress(
        Binary.FromText(
          "i45WMlTSUVKK1YlWMgIyjEz1DY30jQyMDMFCxiAhAxQhE7DyWAA=",
          BinaryEncoding.Base64
        ),
        Compression.Deflate
      )
    ),
    let
      _t = ((type nullable text) meta [Serialized.Text = true])
    in
      type table [OppID = _t, ExpectedCloseDate = _t]
  ),
  #"Changed Type" = Table.TransformColumnTypes(
    Source,
    {{"OppID", Int64.Type}, {"ExpectedCloseDate", type date}}
  )
in
  #"Changed Type"

     

    Quote with merged Opportunity data and NewDate added

    let
  Source = Table.FromRows(
    Json.Document(
      Binary.Decompress(
        Binary.FromText(
          "i45WMlTSQWB9QyN9IwMjQ6VYnWglI6ioEYaMMVTGGEPGBCpjgioTCwA=",
          BinaryEncoding.Base64
        ),
        Compression.Deflate
      )
    ),
    let
      _t = ((type nullable text) meta [Serialized.Text = true])
    in
      type table [QuoteID = _t, ProductID = _t, OppID = _t, Date = _t]
  ),
  #"Changed Type" = Table.TransformColumnTypes(
    Source,
    {{"QuoteID", Int64.Type}, {"ProductID", Int64.Type}, {"OppID", Int64.Type}, {"Date", type date}}
  ),
  #"Merged Queries" = Table.NestedJoin(
    #"Changed Type",
    {"OppID"},
    Opportunity,
    {"OppID"},
    "Opportunity",
    JoinKind.LeftOuter
  ),
  #"Expanded Opportunity" = Table.ExpandTableColumn(
    #"Merged Queries",
    "Opportunity",
    {"ExpectedCloseDate"},
    {"ExpectedCloseDate"}
  ),
  #"Added Custom" = Table.AddColumn(
    #"Expanded Opportunity",
    "NewDate",
    each if [ExpectedCloseDate] = null then [Date] else [ExpectedCloseDate],
    type date
  )
in
  #"Added Custom"

     

    Paste each of these into the advanced editor of blank queries if you prefer to visulise it that way.

    I hope this helps.