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

[D_Brandon_E]

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?

[aj1973]

Hi @D_Brandon_E 

Can you provide a Sample please!

[D_Brandon_E]

@aj1973 

Sample:

ID	Status	StartDate	Milestone1	Milestone2	EndDate
1	Completed	1/3/2021	1/17/2021	3/7/2021	5/18/2021
2	Not Started
3	Milestone1	4/17/2021	6/22/2021
4	Completed	4/18/2021	5/9/2021	9/8/2021	10/2/2021
5	Milestone2	6/7/2021	9/6/2021	10/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 · Accepted solution]

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.