Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
Solved! Go to 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
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
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"
)
Proud to be a 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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
106 | |
98 | |
75 | |
63 | |
62 |
User | Count |
---|---|
135 | |
105 | |
104 | |
80 | |
65 |