DateDiff from 2 tables [it cant be this hard :)]

[AndyNeo]

Hi All

First post and a bit of a newbie!!!!! So bear with me.....  I have 2 tables of data which is basically the same.  One set of data is just 2 weeks older.

What I am trying to do a date difference between the 2.

Table1

Computer Name 

Date Scanned

Table2 

Computer Name

Date Scanned

I am trying to work out the date difference between the date scanned.  There a relationship on between the tables based on computer name... There some other measures, calculated fields etc in the Table1 

I have tried various variation of DateDiff  including add related / MAX / MIN etc etc and still stuck....

Basically I want to do on Table2 a

DayDifferent = DATEDIFF(Table1[Date Scanned], Table2[Date Scanned],DAYS)  <-- this is an example btw....

It wont  list the Table1 field and just calculated fields if I do something this....

Related throws up a relationship problem

and MAX just give me 1 numbers as it is get MAX value for the date.... when I want it to do it for all of them

Do I need to put in Computer name to match ie IF computer from table1and table 2 is equal do a date diff.....

Completely stuck 😐 :S

Any help will be greatly apprieciated

[Fowmy]

@AndyNeo 
Have you tried the LOOKUPVALUE function: https://docs.microsoft.com/en-us/dax/lookupvalue-function-dax

Video: https://www.youtube.com/watch?v=iJfzfYt5Qws&t=0s

________________________

If my answer was helpful, please consider Accept it as the solution to help the other members find it

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube  LinkedIn

[mahoneypat]

FYI that if your two tables only differ on dates, you should append the two tables to have a single table in your model.  

For your current model, if the relationship is 1:1 or 1:Many from Table2 to Table1 (and they are related on computer name), then this expression should work as a calculated column on Table2

Diff = DATEDIFF(MAX(Table1[Date Scanned]), Table2[Date Scanned], DAY)

Again, you really should append those tables, in which you will then need a different expression.  And I would suggest you do this analysis as a measure, and not as a column.  And add a Date table to your model.  Ok, enough preaching.

Regards,

Pat

[AndyNeo]

Hi Pat

Are you saying to append the tables and then try that forumla....?

If I try that forumla as a measure I am only getting the existing measures list for use in the formula?

I think the problem maybe one table has 20000 rows and the other has 20100 rows.....  

[v-yingjl · Accepted solution]

Hi @AndyNeo ,

If the relationship is 1:1 or 1:*, you can create the diff measure like this:

_Diff = 
DATEDIFF (
    CALCULATE (
        MAX ( 'Table 1'[Date Scanned] ),
        FILTER (
            ALL ( 'Table 1' ),
            'Table 1'[Computer Name] in DISTINCT('Table 2'[Computer Name])
        )
    ),
    MAX('Table 2'[Date Scanned]),
    DAY
)

If the relationship is *:*, you may want to calcualte the datediff for each computer of each row in table2, in this case, need to create index column in these two tables as auxiliary column in power query(see the steps in my below sample), then create a measure like this:

__Diff = 
DATEDIFF (
    CALCULATE (
        MAX ( 'Table_1'[Date Scanned] ),
        FILTER (
            ALL ( Table_1 ),
            'Table_1'[Computer Name]
                IN DISTINCT ( 'Table_2'[Computer Name] )
                    && 'Table_1'[Index] IN DISTINCT ( 'Table_2'[Index] )
        )
    ),
    CALCULATE (
        MAX ( 'Table_2'[Scanned] ),
        FILTER (
            ALL ( Table_2 ),
            'Table_2'[Computer Name]
                IN DISTINCT ( 'Table_1'[Computer Name] )
                    && 'Table_2'[Index] IN DISTINCT ( 'Table_2'[Index] )
        )
    ),
    DAY
)

left is 1:1, right is *:*

Attached a sample file in the below, hopes to help you.

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.