A table of multiple values was supplied where a single value was expected.

[harshad_barge]

I am trying to see if the "Calculated Date" values fall between "Dim_Employee_Position.From" and "Dim_Employee_Position.To" dates.
I am using DATESBETWEEN function. Its not working.

What I am doing wrong?

Boundary =

IF ( DATESBETWEEN('Ordinary Hours'[Calculation Date], [Dim_Employee_Position.From], [Dim_Employee_Position.To]),

BLANK() , "Out of Bounds"

)

All are date time fields

Thanks,

Harshad

[v-gizhi-msft · Accepted solution]

Hi,

Please try to change your original column to this:

Boundary =
IF (
    'Ordinary Hours'[Calculation Date]
        IN DATESBETWEEN (
            'Ordinary Hours'[Calculation Date],
            [Dim_Employee_Position.From],
            [Dim_Employee_Position.To]
        ),
    BLANK (),
    "Out of Bounds"
)

Best Regards,

Giotto Zhi

[danextian]

Hi @harshad_barge ,

As per MS documentation, DATESBETWEEN alone "Returns a table that contains a column of dates that begins with the start_date and continues until the end_date."  You are getting an error because it is not returning a single value but a table of dates. It is used together with CALCULATE to get a single value. 

If you need to check whether a date is within two dates, you can just use a simple IF formula. 

Boundary =
IF (
    'Ordinary Hours'[Calculation Date] >= 'Ordinary Hours'[Dim_Employee_Position.From]
        && 'Ordinary Hours'[Calculation Date] <= 'Ordinary Hours'[Dim_Employee_Position.To],
    BLANK (),
    "Out of Bounds"
)

[Greg_Deckler]

DATESBETWEEN is going to return a table of values. Specifically, it will return a table of all the dates between the parameters specified. You need to compare it to something to get a logical TRUE/FALSE statement.

Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490