Forum Discussion
DatesBetween when both dates are connected to Date table but none are primary
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 ZhouIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Community ChampionHi D_Brandon_E
Can you provide a Sample please!
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.
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 ZhouIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.