Forum Discussion
DATESBETWEEN function error
HI ChihiroWa ,
DATESBETWEEN is a table function and will return a table of dates if used in creating a calculated table. If used in a measure, it is to filter an aggregation and is to be used together with CALCULATE. The correct syntax is something like
=
CALCULATE (
[Measure],
DATESBETWEEN (
TableB,
FIRSTDATE ( 'TableA'[Period - start] ),
LASTDATE ( 'TableA'[Period - end] )
)
)
danextian ,Thanks a lot!
I found using DATESBETWEEN is not a good way to solve my problem.
If you have, could you tell me some other better ways ?
The answer really depends on your use case but information provided isnt enough to propose more appropriate one. What is the data used in your area chart? Is that volume? If so, your field pane should look something like this.
Also, the formula below will not even return a table of dates as you are comparing a year against a date.
= DATESBETWEEN ( 'TableB'[CY], FIRSTDATE ( 'TableA'[Period - start] ), LASTDATE ( 'TableA'[Period - end] ) )Just DATESBETWEEN should be something like this. In your example, CY is year.
DATESBETWEEN(<Dates>, <StartDate>, <EndDate>)Yes, the area chart shows volume, and field pane looks something like that.
Actually, Table B has thousand of rows, sorry for making confuse you.
As to CY, I prepared 'CY[date]' to recognize CY as a date.
CY[date] = 'TableB'[CY])&"/01/01"