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.
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!
Solved! Go to Solution.
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.
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.
Hi Greg, i´m quiet new for coding, hope you could help me.
I have the same problem. I want to figure out wether a given date in one column is a date between that date and TODAY()
My code is like this:
Kolonne 4 = IF(COUNTX(DATESBETWEEN(Tabel1[DateOne];TODAY()>0;1;0)))
The error says somthing like this: To few arguments for the function DATESBETWEEN, there must be at least 3 argument for this function.
Sorry - I would like to make a new tread bud dont have the button for that, so i try to put my question here.
Best regards
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 |
---|---|
109 | |
96 | |
77 | |
66 | |
53 |
User | Count |
---|---|
144 | |
105 | |
102 | |
89 | |
63 |