Forum Discussion

ChihiroWa's avatar
ChihiroWa
Frequent Visitor
3 years ago

DATESBETWEEN function error

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

 

 Prepared tables are like below:

 Table A : a period of item production

CountrySegmentsItem namePeriod - startPeriod - end
JapanAxxx2013/1/12015/12/31
JapanAyyy2010/1/12020/12/31
JapanBzzz2020/1/12025/12/31
IndiaAaaa2018/1/12020/12/31
IndiaCbbb2040/1/12045/12/31
IndonesiaBccc2030/1/12035/12/31
IndonesiaCddd2025/1/12030/12/31
・・・・・・・・・・・・・・・

 

 Table B : global market volume

CountyCYVolume
Japan20201000000
Japan20211200000
Indonesia20202000000
・・・・・・・・・

 

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

 
General Comment
Need Help
Show and Tell

4 Replies

  • danextian's avatar
    danextian
    Super User
    3 years ago

    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's avatar
      ChihiroWa
      Frequent Visitor
      3 years ago

      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's avatar
        danextian
        Super User
        3 years ago

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