Forum Discussion

D_Brandon_E's avatar
D_Brandon_E
Regular Visitor
4 years ago
Solved

DatesBetween when both dates are connected to Date table but none are primary

Hi everyone,

 

I am working on a project and the datasource I am receiving to work with is not the best but I do not have any control over it. I have a table in which there are around 10-15 seperate date columns for different milestones in a project. One of the dates marks that the particular site project has started and another date column marks the completion of the site project. All dates in this table are connected to the Date table. Both date columns can be BLANK by design as the project has not yet started or is not complete. 

 

I am trying to create a calculated column showing the days to completion of the projects. When I perform a simple DATESBETWEEN function, I keep getting the "A table of multiple values was supplied...." message. 


The function I have written so far with errors is:

Average Time = DATESBETWEEN('Date'[Date], 'Table'[Start Date], 'Table[Finish Date])

 

I even tried to create variables to weed out the projects that have a BLANK is either or both of the date fields. I have tried several iterations to try and get it to not throw an error. I verified that both fields have the same data type (Date). Neither of these fields are the primary connection to the date table. I tried various runs at using "USERELATIONSHIP" but those failed as well.

 

Any thoughts?

Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi D_Brandon_E ,

     

    DATEBETWEEN function will return a table that contains a column of dates that begins with a specified start date and continues until a specified end date. So you will get this error "A table of multiple values was supplied...."  in your calculated table. I suggest you to try DATEDIFF function.

    Complete Datediff = 
IF (
    'Table'[Status] = "Completed",
    DATEDIFF ( 'Table'[StartDate], 'Table'[EndDate], DAY ),
    BLANK ()
)

    Result is as below.

     

    Best Regards,
    Rico Zhou

     

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

     

3 Replies

    • D_Brandon_E's avatar
      D_Brandon_E
      Regular Visitor
      4 years ago

      aj1973 

       

      Sample:

       

      IDStatusStartDateMilestone1Milestone2EndDate
      1Completed1/3/20211/17/20213/7/20215/18/2021
      2Not Started    
      3Milestone14/17/20216/22/2021  
      4Completed4/18/20215/9/20219/8/202110/2/2021
      5Milestone26/7/20219/6/202110/27/2021 

       

      I need to calculate the days between in a calculated column of StartDate and EndDate if Status is Completed. Both StartDate and EndDate are connected to the date table but are not primary. (Original Data set has around 15 date columns and another column is the primary. 

      • Anonymous's avatar
        Anonymous
        Not applicable
        4 years ago

        Hi D_Brandon_E ,

         

        DATEBETWEEN function will return a table that contains a column of dates that begins with a specified start date and continues until a specified end date. So you will get this error "A table of multiple values was supplied...."  in your calculated table. I suggest you to try DATEDIFF function.

        Complete Datediff = 
IF (
    'Table'[Status] = "Completed",
    DATEDIFF ( 'Table'[StartDate], 'Table'[EndDate], DAY ),
    BLANK ()
)

        Result is as below.

         

        Best Regards,
        Rico Zhou

         

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