cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
rachaelwalker
Resolver II
Resolver II

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!

 

1 ACCEPTED SOLUTION
ebeery
Solution Sage
Solution Sage

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



View solution in original post

3 REPLIES 3
KNP
Super User
Super User

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.

 

Have I solved your problem?
Please click Accept as Solution so I don't keep coming back to this post, oh yeah, others may find it useful also ;).
chrome-9xf-Zagzel-B

If you found this post helpful, please give Kudos.
It gives me a sense of instant gratification and, if you give me Kudos enough times, magical unicorns will appear on your screen.
If you find my signature vaguely amusing, please give Kudos.
KIfp67uy-Sr
Proud to be a Super User!PBI-Super-User-Rank-30x30-1x
ebeery
Solution Sage
Solution Sage

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



View solution in original post

This worked! Thank you

Helpful resources

Announcements
Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

User Group Leader Meeting January 768x460.png

Calling all User Group Leaders!

Don't miss the User Group Leader meetings on January, 24th & 25th, 2022.

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors