DATESBETWEEN function Error

[timazarj]

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?

[timazarj]

Thank you all for the help!

[v-cazheng-msft · Accepted solution]

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.

[aj1973]

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.