Added a custom field in Advanced Editor but not usable in relationships

[madkow]

In short, I added a custom date field in the Advanced Editor (M Query) and used it to filter rows

let
    Source = opportunities,
    #"Added Opp Date" = Table.AddColumn(#"Changed Type1", "Opp Date", each if [Opportunity State] = "Open" then [Estimated Close Date] else [Actual Close Date]),
    AddedFilterPY = Table.AddColumn(#"Added Opp Date", "Greater Than Prev Year", each if Date.Year([Opp Date]) >= Date.Year(Date.AddYears(DateTime.LocalNow(),-1)) then "True" else "False"),
    #"Filtered PY" = Table.SelectRows(AddedFilterPY, each ([Greater Than Prev Year] = "True")),

Basically, a conditional date field shows depending on the Opportunity State.  I then use this to only get the prior year and future data.  Don't want to pull all kinds of historical data.

When I tried to link the 'Opp Date' to a Date table the report did not work correctly.  The dates were blank and could not filter on them.  It screwed up the report bad.

I instead had to make a custom field through the designer that did the same condition on State.  This worked.

Oppo Date = IF(Opportunity[Opportunity State] = "Open",Opportunity[Estimated Close Date],Opportunity[Actual Close Date]) 

Just wondering what the technical reason for this is.  Assuming it might be the difference between an M query custom field and a Power BI designer custom field.  Just thought it was odd.

And if anyone has any suggestions for how to do this easier or more efficiently I am all ears.

Thank you

[Greg_Deckler]

Probably a reason for it, but not enough information presented to tell you what might be going wrong.

[madkow]

What kind of information do you need?

[v-huizhn-msft]

Hi @madkow,

You said "When I tried to link the 'Opp Date' to a Date table the report did not work correctly.  The dates were blank and could not filter on them.  It screwed up the report bad." How did you do that? How to link table in query editor, could you please share more details?

Thanks,
Angelia

[madkow]

Hi @v-huizhn-msft

I am sorry if I was unclear.  I did not link in the query editor.

After the column was created in M Query and changes were applied I went back to the designer and linked it through the Relationships window.

When I attempted to link it to my date table, the report did not function as expected.  I've used date tables previously but this was different.  No data was showing.

The workaround was to create the custom column from the designer instead of the query editor.  That worked when linking the opportunity table to the date table.