Forum Discussion

timazarj's avatar
timazarj
Helper II
5 years ago
Solved

DATESBETWEEN function Error

Hello,

 

I need to find the sales to quote ratio for the current month, sales are calculated based on ActionAttemp date column in a table and quotes are calculated based on Createdtime date column in the same table.

for finding the sales based on ActionAttempt date I wrote the following:

SalesActionDate =
CALCULATE(
    SUM('Leads'[SOLD]),
    (DATESBETWEEN('Leads'[lastAction_timestamp],
        {DATE(2021, 3, 1) + TIME(00, 01, 01) },{DATE(2021, 3, 31) + TIME(12, 01, 01)})
))
 
And I got this error for calculation, any suggestion?

 

Need Help
  • v-cazheng-msft's avatar
    v-cazheng-msft
    5 years ago

    Hi timazarj 

    You can make some changes to your Measure as the following if you don't want to create a Calendar table.

     

    SalesActionDate =

CALCULATE (

    SUM ( 'Leads'[SOLD] ),

    FILTER (

        'Leads'[lastAction_timestamp],

        'Leads'[lastAction_timestamp]

            >= ( DATE ( 2021, 3, 1 ) + TIME ( 00, 01, 01 ) )

            && 'Leads'[lastAction_timestamp]

                <= ( DATE ( 2021, 3, 31 ) + TIME ( 12, 01, 01 ) )

    )

)

     

    Here is a sample.

     

    Best Regards

    Caiyun Zheng

     

    Is that the answer you're looking for? If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

     

3 Replies

  • aj1973's avatar
    aj1973
    Community Champion
    5 years ago

    Hi timazarj 

    DATESBETWEEN  is a Time intelligence function therefore you need a Calendar Table in your model. Your actual Leads Table contains duplicate dates, Can't be for the use of DATESBETWEEN.

     

  • v-cazheng-msft's avatar
    v-cazheng-msft
    Community Support
    5 years ago

    Hi timazarj 

    You can make some changes to your Measure as the following if you don't want to create a Calendar table.

     

    SalesActionDate =

CALCULATE (

    SUM ( 'Leads'[SOLD] ),

    FILTER (

        'Leads'[lastAction_timestamp],

        'Leads'[lastAction_timestamp]

            >= ( DATE ( 2021, 3, 1 ) + TIME ( 00, 01, 01 ) )

            && 'Leads'[lastAction_timestamp]

                <= ( DATE ( 2021, 3, 31 ) + TIME ( 12, 01, 01 ) )

    )

)

     

    Here is a sample.

     

    Best Regards

    Caiyun Zheng

     

    Is that the answer you're looking for? If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.