DATESBETWEEN function error

[ChihiroWa]

Hi, I hope someone help me with a DATESBETWEEN function error.

 Prepared tables are like below:

 Table A : a period of item production

Country	Segments	Item name	Period - start	Period - end
Japan	A	xxx	2013/1/1	2015/12/31
Japan	A	yyy	2010/1/1	2020/12/31
Japan	B	zzz	2020/1/1	2025/12/31
India	A	aaa	2018/1/1	2020/12/31
India	C	bbb	2040/1/1	2045/12/31
Indonesia	B	ccc	2030/1/1	2035/12/31
Indonesia	C	ddd	2025/1/1	2030/12/31
・・・	・・・	・・・	・・・	・・・

 Table B : global market volume

County	CY	Volume
Japan	2020	1000000
Japan	2021	1200000
Indonesia	2020	2000000
・・・	・・・	・・・

What I want to do is :  changing x-axis of a graph by selecting country

e.g. Japan -> from 2010 to 2025

・a slicer

・a gragh

What I tried:  DATESBETWEEN function

period for x-axis = DATESBETWEEN(TableB'[CY],FIRSTDATE('TableA'[Period - start]),LASTDATE('TableA'[Period - end]))

error : 

Thanks,

Chihiro

[danextian]

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] )
    )
)

[ChihiroWa]

@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 ?

[danextian]

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>)

[ChihiroWa]

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"