Forum Discussion

aadass's avatar
aadass
Frequent Visitor
8 years ago
Solved

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

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([R...
aadass's avatar
aadass
Frequent Visitor
8 years ago

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's avatar
v-chuncz-msft
Community Support
8 years ago

aadass,

 

Given the relationship, you may use RELATEDTABLE Function.

MAXX ( RELATEDTABLE ( Invoices ), Invoices[InvoiceDate] )
  • aadass's avatar
    aadass
    Frequent Visitor
    8 years ago

    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