Forum Discussion

LukasLK's avatar
LukasLK
New Member
2 years ago
Solved

Relationship between date columns in different tabels and comparting dates

Hello,

I have two tables including columns with data and project number among others. I need to create new column in one of the table as a result of comparison of dates in both tables. I am not able to do it. I tried couple of ways but it´s still complaining about relationships. When I created relationship between "ConvertedDate" and "Quotastion due Date" it create inactive relationship (I have already active relationship between project numbers). I tried to use USERELATIONSHIP in DAX, but still without success. Do you have any idea? I thought it´easy to do this, but I spent alredy way to much time on this... 
Here my formula:

QuotationStatus =
IF (
    USERELATIONSHIP('Table 1 (FEA History)'[ConvertedDate], 'PRF Decision'[Quotation due date]),
    IF (
        RELATED('PRF Decision'[Quotation due date]) > 'Table 1 (FEA History)'[ConvertedDate],
        "In Quotation",
        "After Quotation"
    ),
    BLANK()  -- Or any default value when the relationship is not active
)
 
For this, it returns:
The column 'PRF Decision[Quotation due date]' either doesn't exist or doesn't have a relationship to any table available in the current context.
  • tamerj1's avatar
    tamerj1
    2 years ago

    Hi LukasLK 
    I'm not sure what exactly are expecting to get. However, please try

    QuotationStatus =
IF (
    CALCULATE (
        MAX ( 'PRF Decision'[Quotation due date] ),
        USERELATIONSHIP ( 'Table 1 (FEA History)'[ConvertedDate], 'PRF Decision'[Quotation due date] )
    ) > 'Table 1 (FEA History)'[ConvertedDate],
    "In Quotation",
    "After Quotation"
)

2 Replies

  • tamerj1's avatar
    tamerj1
    Icon for Community Champion rankCommunity Champion
    2 years ago

    Hi LukasLK 
    I'm not sure what exactly are expecting to get. However, please try

    QuotationStatus =
IF (
    CALCULATE (
        MAX ( 'PRF Decision'[Quotation due date] ),
        USERELATIONSHIP ( 'Table 1 (FEA History)'[ConvertedDate], 'PRF Decision'[Quotation due date] )
    ) > 'Table 1 (FEA History)'[ConvertedDate],
    "In Quotation",
    "After Quotation"
)