[Help Needed] - How to compare and get values from two tables without a relationship

[Anonymous]

Hi Everyone,

Requesting your help in making a table with Dax

Attached a image above explaining the need.

Table 1 has three columns . Table 2 has two columns.

Output should be from Table 1 - Serial Numbers which are Shipped,but the values should be selected only when Date in table 2 is more than 5 days compared to Date in Table 1.

There is no relationship between tables.
@amitchandak 

Thanks in Advance. 🙂

[Anonymous · Accepted solution]

Hi  @Anonymous ,

Here are the steps you can follow：

1. Create calculated column.

Date2 =
CALCULATE(MAX('Table2'[Date]),FILTER(ALL(Table2),'Table2'[Serial Number]=EARLIER('Table1'[Serial Number])))

Day =
DATEDIFF('Table1'[Date],'Table1'[Date2],DAY)

2. Create calculated table.

Table =
var _1=FILTER(ALL(Table1),'Table1'[Day]>=5&&'Table1'[Status]="Shipped")
return
SUMMARIZE(
    _1,
    'Table1'[Serial Number],'Table1'[Date],Table1[Status],Table1[Day])

3. Result:

Best Regards,

Liu Yang

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

[ChrisClever]

Hey @Anonymous , take a look at this DAX code.

TimeDifference =
CALCULATE (
    DATEDIFF (
        SELECTEDVALUE ( 'Table 1'[Date] ),
        SELECTEDVALUE ( 'Table 2'[Date] ),
        DAY
    ),
    TREATAS ( VALUES ( 'Table 1'[Serial Number] ), 'Table 2'[Serial Number] )
)

This is how you can calculate the time difference.

From there just use filters.

[Anonymous]

Thanks for the reply @ChrisClever , I tried this. But for some reason, output is coming as blanks