DateDIFF between multilpe tables /Cross tables

[schoden]

Hi experts, 

HOw Can I achieve DATEDIFF between different tables as shown.  RELATED function is not working.

Vendor_Invoice_Date in Table2 and Date_invoice in Table 1. 

[v-lili6-msft]

HI @schoden 

For your case, you may just use this simple formula to create a new column

Column = DATEDIFF(Table1[Date_invoice],CALCULATE(MAX(Table2[Vendor_Invoice_Date])),DAY)

Regards,

Lin

[schoden]

@v-lili6-msft 

Thank for the reply.....It almost matched but some didnt show correct figure as shown

[v-lili6-msft]

hi @schoden 

If so, since in your model, between the two date, relationship is like man to many, I would suggest you create a measure instead of column as below

Measure = DATEDIFF(CALCULATE(MAX(Table1[Date_invoice])),CALCULATE(MAX(Table2[Vendor_Invoice_Date])),DAY)

Regards,

Lin

[schoden]

@v-lili6-msft With this measure the query takes times to load., the dotted circle keeps on circling.

[amitchandak]

@schoden , you need to take date diff at the common dimension level

refer https://community.powerbi.com/t5/Community-Blog/Decoding-Direct-Query-in-Power-BI-Part-2-Date-Differ...

[schoden]

@amitchandak  Thanks as always but since my two wanted tables has no common field to link , it is difficult to use the formula

😞

[amitchandak]

@schoden , there need to be some common ground to take diff.

[tex628]

If there is no common field to link, how do you know which date is related to which? 

/ J

[schoden]

@tex628as you can see in the picture , it gets linked by 3 tables inbetween those two table1 and table 2.

[tex628]

In this table:



Does the related() work if you try and create two columns for the dates?

/J

[schoden]

@tex628Can you explain more, which table should I create the the date column.    Becuase using RELATED function in table1 can pick automatically only table around it, that is the 3 tables linked to it but not the bridge/joining tables.

[tex628]

You need to create the column in the brdigetable

[schoden]

@tex628  Oki I will try to implement in the bridge table..and get back to you soon