cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

find Date diff from two different tables

Hi all, 

 

I am trying to find a DAX to calculate the Datediff between PODATE (from PO table) and INVOICEDATE (from invoice table). The two tables are linked PONUMBER.

I have tried 

date diff = DATEDIFF('DiASCII_850 (PO)'[PONUMBER],RELATED('DiASCII_810 (invoice)'[INVOICEDATE]),DAY)
but it keeps giving me this error: INVOICEDATE either doesnt exist or doesn't have a relationship to any available in the current context. 

 

Thank you for any help. 

 

date diff.PNG

 

 

2 REPLIES 2
Community Support Team
Community Support Team

Re: find Date diff from two different tables

Hi phuynh242

According to your description, it seems that you want to get datediff  based on  table’s date column. But in your expression, it seems that you are calculating on date and ponumber. You need to use corrected expression. You could create a calculated column in PO table like below

Capture7.PNG

datedif =
DATEDIFF ( PO[PODATE], RELATED ( INVOICE[INVOICEDATE] ), DAY )

Best Regards,
Zoe Zhi

Anonymous
Not applicable

Re: find Date diff from two different tables

Hi dax, thank you for your solution. I tested it and it works with your DAX when I have a "Many to One" or "One to One" relationship between the two tables.

 

However, in my model the PO and the INVOICE has a "Many to Many" relationship, I think that is why it gives me this error when I tried to write the DAX for datediff with RELATED. I am not sure how to make it works in this case. 

 

My PO table has dublicate PO number with unique PO-ID. My INVOICE table also has dublicate PO number with unique INVOICE-ID. Some PO do not have INVOICE and vice versa. The only cardinality I can choose for this relationship between the two table is "Many to Many". 

 

Ultimately, I am trying to find how long does it take from a PODATE until INVOICEDATE, but one PO can have many invoices. I created a column to calculate the last invoice date per PO already. But I cannot figure out how to calculate datediff across two different table with "Many to Many" cardinality in this case. 

 

I hope my explanation is not too confusing. Thank you very much and appreciated if you have any suggestion. 

 

 

Helpful resources

Announcements
New Kudos Received Badges Coming

New Kudos Received Badges Coming

Kudos to you if you earned one of these! Check your inbox for a notification.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Kudoed Authors (Last 30 Days)
Users online (3,665)