Problem to use DATEDIFF function between two dates which located in two not directly related tables

[aadass]

Problem to use DATEDIFF function between two dates which located in two but not directly related tables.

Task: Stock.Registration_Date - Invoices.InvoiceDate

Formula: NeedDate= DATEDIFF([Registration_Date],RELATED(Invoices[InvoiceDate]),WEEK)

Err: The column 'Invoices[InvoiceDate]' either doesn't exist or doesn't have a relationship to any table available in the current context

Comments: There two tables "Stock" and "Invoice" - they are related through table in between "InvoiceLines".

Thank you

[Phil_Seamark]

Hi @aadass

Are you trying to create a new column in the 'Stock' table or the 'InvoiceLines' table?

[aadass]

For now  "Stock" table..  - where i am trying to create new Measure. (In the future this New Measure should be used in

report(Visualization)

but i mentioned those three tbls because tbl InvoiceLines is in between two Main tbls "Stock" and "Invoices"

I need one field from Stock and one from Invoices (they are not linked directly - but only through InvoiceLines )

tnx

Angelika

[v-chuncz-msft · Accepted solution]

@aadass,

Given the relationship, you may use RELATEDTABLE Function.

MAXX ( RELATEDTABLE ( Invoices ), Invoices[InvoiceDate] )

[aadass]

Sam,

i think it solved my issue:

Weeks in stock = DATEDIFF([Registration_Date],MAXX(RELATEDTABLE(Invoices),Invoices[InvoiceDate]),WEEK)

I would never thought that MAXX  can act as a little liason between "unrelated" tables 

Thank you much

Angelika