Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Power BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. Register now.

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

@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
Phil_Seamark
Microsoft Employee
Microsoft Employee

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!

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

 

@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.

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
June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

May 2025 Monthly Update

Fabric Community Update - May 2025

Find out what's new and trending in the Fabric community.