Calculate days between dates in different tables

[Anonymous]

Can someone please provide me with assistance. I have 2 tables which are joined on a Project # and need to find the number of days between Created On Date  and Project Start Date. Below is an example of what I am looking to achive.

(Table 1 [Created On])	(Table 2 [Project Start Date])	Desired Result
1/5/2022	5/5/2022	120
2/18/2022	3/1/2022	11

Thanks for any help!

[ziddmakk]

To calculate the days between the "Created On" date from Table 1 and the "Project Start Date" from Table 2, assuming they are joined on a Project #, you can use a simple date difference formula in a database query or spreadsheet. For example, in SQL, you could write: SELECT DATEDIFF(t2.Project_Start_Date, t1.Created_On) AS Days_Between FROM Table1 t1 JOIN Table2 t2 ON t1.Project_Number = t2.Project_Number. For your specific example, this would yield 120 days between 1/5/2022 and 5/5/2022 (accounting for inclusive dates and a leap year), and 11 days between 2/18/2022 and 3/1/2022. In a spreadsheet like Excel, use =(Table2!Project_Start_Date - Table1!Created_On) and format the result as a number, giving you 120 and 11 respectively. Adjust based on your tool and whether you need inclusive or exclusive day counts.

[speedramps]

fromats your dates correctly eg dd/mm/yyyy depending on your region

create a  relationship from table1 project id to table2 project id 

create dax measure 

daysage =

DATEDIFF(

SELECTEDVALUE(Table1[CREATED_ON]),

SELECTEDVALUE(Table2[PROJ_ST_DT]),

DAY

)

create a table visua; and drag:-

table1 PROJ_NBR

table 1 CREATED_ON

table 2 PROJ_ST_DT

daysage 

please click thumbs up and accept as solution buttons. Thank you ! 😎

[Anonymous]

Table 1		Table 2
PROJ_NBR	CREATED_ON		PROJ_NBR	PROJ_ST_DT	Desired Results
1234	1/5/2022		1234	5/5/2022	120
6854	2/18/2022		6854	3/1/2022	11
8544	5/3/2022		8544		0

[rohit_singh]

Hi @Anonymous ,

Please try creating a calculated column on your table as shown below :

Duration =

var _datediff = DATEDIFF('DatesBetween'[CREATED_ON],'DatesBetween'[PROJ_ST_DT],DAY)

return

if (isblank(_datediff), 0 , _datediff)

This should give you the desired result.

Kind regards,

Rohit

Please mark this answer as the solution if it resolves your issue.
Appreciate your kudos! 😊

[amiller5]

Does this still work if the relationship between the tables is many to many? When I try to add a calculated column with DATEDIFF it wont search for the other table as the second variable.

[speedramps · Accepted solution]

Hi rohit_singh

Thanks for your help, but please check your solution suggetion .....

var _datediff = DATEDIFF('DatesBetween'[CREATED_ON],'DatesBetween'[PROJ_ST_DT],DAY)

Wont the DatesBetween'[CREATED_ON]  return a buggy table value ? 🤔

You need to wrap it in a SELETEDVALUE, MIN or MAX to get a scalar value.

See  method I suggested.

Many thanks 😎

[rohit_singh]

Hi @speedramps ,

Thanks for your feedback. I have created a calculated column and not a measure so it will work as expected. 

[Anonymous]

The first one I created an excel spreadsheet when adding the details of my request and the last one was a copy of an external excel. If you let me know how to attach an excel document I will upload it.

[speedramps]

You can copy excel cells and paste them into the chat as a table.

This is safer than sending excel files which can contain nasty malware

[speedramps]

provide example input tables as tables (not a screen shots, so we cam import then quickly create a solution)

 Thanks