Calculate Date difference of two dates in two different tables.

[krrish116]

Hi @amitchandak 

I have 4 tables

Date Table -->which it contains Date, Year, Month-Year, Quarter-Year

Product Table --> which it contains Date, Product Value, Product ID

Customer Table --> which it contains Date, Product ID, Customer Name.

I created Product ID Master Table 

Product ID Master = DISTINCT(

UNION(

VALUES('Customer'[Product ID]),

VALUES('Product'[Product IDNotification No.])))

Now the Relationships are from Date table to Customer & Product table with one to many for Date Column

Product ID Master Table to Customer & Product table with many to many for Product ID Column.

The Problem is now i have to calculate the DateDiff of two dates in two tables.

I created a column

Diff = DATEDIFF(Product[Date],MAXX(RELATEDTABLE('Customer'), 'Customer'[Date]),DAY)

but it is not working..

please help me in sort out this.

Thanks,

Krish...

[Greg_Deckler · Accepted solution]

@krrish116 Sorry, having trouble following, can you post sample data as text and expected output? Also, an image of the relationships would be very helpful.

Check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.

[v-angzheng-msft · Accepted solution]

Hi, @krrish116 

Try the formula below:

Diff =
DATEDIFF (
    'Product'[Date],
    MAXX (
        FILTER ( 'Customer', 'Customer'[Product ID] = 'Product'[Product ID] ),
        'Customer'[Date]
    ),
    DAY
)

Or, please share your sample pbix file's link here, then I can try to look into it to come up with a more accurate formula.

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

[v-angzheng-msft · Accepted solution]

Hi, @krrish116 

Try the formula below:

Diff =
DATEDIFF (
    'Product'[Date],
    MAXX (
        FILTER ( 'Customer', 'Customer'[Product ID] = 'Product'[Product ID] ),
        'Customer'[Date]
    ),
    DAY
)

Or, please share your sample pbix file's link here, then I can try to look into it to come up with a more accurate formula.

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

[krrish116 · Accepted solution]

Hi,

Its working fine Thanks.

-Krish...

[Greg_Deckler · Accepted solution]

@krrish116 Sorry, having trouble following, can you post sample data as text and expected output? Also, an image of the relationships would be very helpful.

Check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.