Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
User | Count |
---|---|
124 | |
106 | |
99 | |
63 | |
59 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |