Forum Discussion
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
datedif = DATEDIFF ( PO[PODATE], RELATED ( INVOICE[INVOICEDATE] ), DAY )
Best Regards,
Zoe Zhi
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.