Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Calculate days between dates in different tables

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/20223/1/202211

 

Thanks for any help!

 

Need Help
  • speedramps's avatar
    speedramps
    4 years ago

    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 😎

     

10 Replies

  • speedramps's avatar
    speedramps
    Icon for Super User rankSuper User
    4 years ago

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

     

     Thanks

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago
    Table 1 Table 2 
    PROJ_NBRCREATED_ON PROJ_NBRPROJ_ST_DTDesired Results
    12341/5/2022 12345/5/2022120
    68542/18/2022 68543/1/202211
    85445/3/2022 8544 0
    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      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's avatar
        speedramps
        Icon for Super User rankSuper User
        4 years ago

        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

    • rohit_singh's avatar
      rohit_singh
      Icon for Solution Sage rankSolution Sage
      4 years ago

      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! 😊

      • speedramps's avatar
        speedramps
        Icon for Super User rankSuper User
        4 years ago

        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 😎

         

  • speedramps's avatar
    speedramps
    Icon for Super User rankSuper User
    4 years ago

    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 ! 😎
  • ziddmakk's avatar
    ziddmakk
    Regular Visitor
    1 year ago

    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.