Forum Discussion
Understanding DATESBETWEEN
Could anyone tell me what is the difference between the following two expressions?
IF (DATESBETWEEN(Table[DateColumn], TODAY(), CustomTable[LastOfMonth]), 1, 0)
IF (DATESBETWEEN(Table[DateColumn], CustomTable[LastOfMonth], TODAY()), 1, 0)
The first one fails - "A table of multiple values was supplied where a single value was expected"
Just the order of the dates in comparison changed. The first one makes more sense because you would say,
"date between 'begin date' and 'end date'". Today is always less than or equal to the end of month.
Definition of other columns in the expression is:
CustomTable[LastOfMonth] = EOMONTH(CustomTable[AsOfDate],0)
CustomTable[AsOfDate] = TODAY()-1
Thanks!
Nope, I messed up. The issue is that you are returning a table of values to your IF statement and it is getting confused by that as it is expecting a TRUE/FALSE or numeric value. So, you would need to do this:
Measure = IF (COUNTX(DATESBETWEEN(Table[DateColumn], TODAY(), CustomTable[LastOfMonth]),[DateColumn])>0, 1, 0)
DATESBETWEEN will return a single column table of dates between the specified dates. But, that is not a valid input to the first argument of an IF function, so you have to do something with that table of dates, like count how many there are.
4 Replies
It is not documented very well https://docs.microsoft.com/en-us/dax/datesbetween-function-dax but DATESBETWEEN only accepts a date column reference as its first argument.
Thanks, Greg.
Does that mean local functions such as TODAY() or its derivates cannot be used with DATESBETWEEN?
Any suggestions to achieve this pseudo code?
[DateColumn1] between <current_date> and <current_month_end_date>
[DateColumn1] between <current_date> and [DateColumn2]
This is not an uncommon scenario!
Nope, I messed up. The issue is that you are returning a table of values to your IF statement and it is getting confused by that as it is expecting a TRUE/FALSE or numeric value. So, you would need to do this:
Measure = IF (COUNTX(DATESBETWEEN(Table[DateColumn], TODAY(), CustomTable[LastOfMonth]),[DateColumn])>0, 1, 0)
DATESBETWEEN will return a single column table of dates between the specified dates. But, that is not a valid input to the first argument of an IF function, so you have to do something with that table of dates, like count how many there are.
