Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
harshad_barge
Helper I
Helper I

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

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
 
Capture.PNG
 
Thanks,
Harshad
1 ACCEPTED SOLUTION
v-gizhi-msft
Community Support
Community Support

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

View solution in original post

3 REPLIES 3
v-gizhi-msft
Community Support
Community Support

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
Super User
Super User

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"
)

 






Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Learn with me on YouTube @DAXJutsu.
Greg_Deckler
Super User
Super User

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


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.