Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Anonymous
Not applicable

Date Difference between 2 different tables

I have 2 date fields from 2 different tables. I want to find the date difference between the date fields. So, I created a measure as follows:

 

Measure = DATEDIFF ( SELECTEDVALUE ( 'Table 1'[Start_date] ), SELECTEDVALUE ( 'Table 2'[End_date] ), DAY )

 

This is not working for the huge amount of data, its only loading . If I want to create a column, what is the dax for it. What is the solution for this?

1 ACCEPTED SOLUTION

Hi @Anonymous ,

You can try this calculate column :

 

Column = DATEDIFF(MAXX(RELATEDTABLE(Table4),'Table4'[Start Date]),'Table5'[End Date],DAY)
 
Here is the demo, please try it:(refer table4 and table5)
 
Best Regards,
Yingjie Li
 
If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

 

View solution in original post

8 REPLIES 8
v-yingjl
Community Support
Community Support

Hi @Anonymous ,

First you should create relationship between two tables:

1:1 relationship:

R1.png

 

For 1:1 relationship you can use this calculate column in 1 site:

Column = DATEDIFF(Table1[Start Date],RELATED(Table2[End Date]),DAY)

 

1:* relationship:

R2.png

 

For 1:* relationship, you can use this calculate column in 1 site:

Column = DATEDIFF(MAXX(RELATEDTABLE(Table3),'Table3'[Start Date]),Table2[End Date],DAY)

 

Or you can use another calculate column in * site:

Column = DATEDIFF(Table3[Start Date],RELATED(Table2[End Date]),DAY)

 

Here is the demo , please try it:

PBIX 

 

1:1 table relationship, please refer table1 and table2, 1:* table relationship, please refer table2 and table3

 

Best Regards,

Yingjie Li

 

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

Anonymous
Not applicable

Hi,

 

In my scenario, the relationship between the dimension table and fact table is 'many-to-many' . Could I know what is the solution for it?

Hi @Anonymous ,

You can try this calculate column :

 

Column = DATEDIFF(MAXX(RELATEDTABLE(Table4),'Table4'[Start Date]),'Table5'[End Date],DAY)
 
Here is the demo, please try it:(refer table4 and table5)
 
Best Regards,
Yingjie Li
 
If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

 

Excellent solution.  Thank you. !!!

Anonymous
Not applicable

Wow, thank you so much. It works

Anonymous
Not applicable

Scenario 1:

Table 1:

ID

F_Nol_ dt

Recovery_dt

1

11-Feb-2020

11-Feb-2020

 

Table 2:

ID

Closing_dt

1

14-Feb-2020

In above scenario, if I calculate the date difference using the Dax

Date_diff = DATEDIFF(Table1[Recovery_dt],MAXX(RELATEDTABLE(Table2),Table2[Closing_dt]),DAY)

I am getting a correct value of date difference in a many to many relationship.

Output:

ID

F_Nol_ dt

Closing_dt

Date_diff

1

11-Feb-2020

11-Feb-2020

3

 

Scenario 2:

Table 3:

ID

F_Nol_ dt

Recovery_dt

1

11-Feb-2020

11-Feb-2020

1

11-Feb-2020

12-Feb_2020

 

Table 4:

ID

Closing_dt

1

14-Feb-2020

2

19-Feb-2020

 

In above scenario, if I calculate the date difference using the Dax

Date_diff = DATEDIFF(Table3[Recovery_dt],MAXX(RELATEDTABLE(Table4),Table4[Closing_dt]),DAY)

I am not getting a correct value of date difference in a many to many relationship.

Output:

ID

F_Nol_ dt

Closing_dt

Date_diff

1

11-Feb-2020

11-Feb-2020

6

 

Greg_Deckler
Super User
Super User

If you just want the number of days, then you can just subtract the two dates from one another and multiply by 1.0. Are the two tables related to one another and is it 1:1 or 1:many or ? 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


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

Can you explain a little better how the data model is structured? Are the two tables linked through a relationship? Are they dimensions to another table?

If they are linked through a relationship you can create a column like: 

Datediff(Table1[Start_date],RELATED(Table2[End_date]),DAY)


Then SUM the result column in a measure and display it in a chart

If they are dimensions to another table you have to use a related also on the first table to retrieve the value from both tables. 


Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors