Referencing multiple date fields in a DAX CALCULATE function

Question

I have a DAX CALCULATE statement that works fine with a single date:

.Placements = CALCULATE
(
count(client[client_id]), 
DATESBETWEEN(client[client_start],[FirstDayOfYear],[EndDate])
)

...but if I work in a second date, it fails:

.Placements = CALCULATE
(
count(client[client_id]), 
or(DATESBETWEEN(client[client_start],[FirstDayOfYear],[EndDate]), 
DATESBETWEEN(client[client_county_court2_start],[FirstDayOfYear],[EndDate]))
)

...and I get the message "A function 'DATESBETWEEN' has been used in a True/False expression that is used as a table filter expression. This is not allowed."

How come I can use one DATESBETWEEN but not two? What's the correct way to do this? Thanks in advance for any help!

Darwin

Aldert Aldert · Accepted Answer · 2020-06-26T15:14:53

The reason is that DATESBETWEEN is returning you a table with a column of dates.

You are better of using the expression in the Calculate method, I believe thi is what you are looking for:

.Placements =
var date1 = client[client_start]
var date2 = client[client_county_court2_start]
return CALCULATE
    (
    COUNT(client[client_id]),
    client, (date1 >= [FirstDayOfYear] && date2 < [EndDate]) || (date1 >= [FirstDayOfYear] && date2 < [EndDate])
    )

Referencing multiple date fields in a DAX CALCULATE function

1 Answers