Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

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. 

 

 

 

Need Help

2 Replies

  • dax's avatar
    dax
    Community Support
    7 years ago

    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

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      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.