Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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
Solved! Go to Solution.
Given the relationship, you may use RELATEDTABLE Function.
MAXX ( RELATEDTABLE ( Invoices ), Invoices[InvoiceDate] )
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
Given the relationship, you may use RELATEDTABLE Function.
MAXX ( RELATEDTABLE ( Invoices ), Invoices[InvoiceDate] )
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
Check out the September 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
114 | |
94 | |
89 | |
35 | |
35 |
User | Count |
---|---|
153 | |
99 | |
82 | |
63 | |
54 |