Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Compare two dates from different tables from DataSet, DaxStudio

Hi, I am trying to compare two dates from different tables, I was trying to use Related function, but it does not work. 

Are you able to help me with this topic? I am using DataSet, with tables and measures. 

 

DEFINE
 // COLUMN 'Delivery Date'[CompareDates] = DATEDIFF('Delivery Date'[DLVD Date],RELATED('Reception Date'[RCTD Date]),Day)
    VAR filter_1 =
        FILTER (
            VALUES ( 'Material'[MAT Business ID] ),
            'Material'[MAT Business ID] = "XX"
        )
    VAR filter_2 =
        FILTER (
            VALUES ( 'Creditor Supplier'[POCS Description] ),
            ( 'Creditor Supplier'[POCS Description] <> "XX" )
                && ( 'Creditor Supplier'[POCS Description] <> "YY" ))
              
EVALUATE
SUMMARIZECOLUMNS (
    'Material'[MAT Business ID],
    'Delivery Date'[DLVD Year-Week],
    'Purchase Order'[PO Number],
    'Delivery Date'[DLVD Date],
    'Reception Date'[RCTD Date],
  //  'Delivery Date'[CompareDates],
    'Material'[MAT Supplier Id],
    'Manufacturer'[MAN Description],
    filter_1,
    filter_2,
    "Purchase Order Ordered Quantity", [Purchase Order Ordered Quantity]
)

 

Error:

 

Need Help
Tips and Tricks

1 Reply

  • v-chenwuz-msft's avatar
    v-chenwuz-msft
    Community Support
    4 years ago

    Hi Anonymous ,

     

    You need create a relationship between 'Delivery Date' and 'Reception Date' when use RELATED() function. 

    Or you can try another way to get the related date. Some dax like the following:

    DATEDIFF (
    'Delivery Date'[DLVD Date],
    MAXX (
        FILTER (
            'Reception Date',
            [RCTD Date] = EARLIER ( 'Delivery Date'[DLVD Date] )
        ),
        [RCTD Date]
    ),
    DAY
)

     

    Best Regards

    Community Support Team _ chenwu zhu

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.