cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
aadass
Frequent Visitor

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

DATEDIFF.JPG

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

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support
Community Support

Re: Problem to use DATEDIFF function between two dates which located in two not directly related ta

@aadass,

 

Given the relationship, you may use RELATEDTABLE Function.

MAXX ( RELATEDTABLE ( Invoices ), Invoices[InvoiceDate] )
Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

4 REPLIES 4
Microsoft Phil_Seamark
Microsoft

Re: Problem to use DATEDIFF function between two dates which located in two not directly related ta

Hi @aadass

 

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


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

aadass
Frequent Visitor

Re: Problem to use DATEDIFF function between two dates which located in two not directly related ta

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

 

Community Support
Community Support

Re: Problem to use DATEDIFF function between two dates which located in two not directly related ta

@aadass,

 

Given the relationship, you may use RELATEDTABLE Function.

MAXX ( RELATEDTABLE ( Invoices ), Invoices[InvoiceDate] )
Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

aadass
Frequent Visitor

Re: Problem to use DATEDIFF function between two dates which located in two not directly related ta

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

Helpful resources

Announcements
New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Join THE global Microsoft Power Platform event series.

Join THE global Power Platform event series.

Attend for two days of expert-led learning and innovation on topics like AI and Analytics, powered by Dynamic Communities.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors