# DAX Calculate with 2 dates • ### Question

• Hi everyone,

I have one doubt

I need sum value1 and value2 where DATE1 = current date and DATE2 = current date

It's correct this?

Total:= CALCULATE(
SUM(Table[Value1]) +  SUM(Table[Valu2]);
FILTER(
ALL(
Value[Date1]);
[Date1] =TODAY()  );

FILTER ( ALL( Table[Date2]); Table[Date2] = TODAY()))

Monday, January 16, 2017 1:19 PM

• There's two obvious errors in the DATESBETWEEN function here: first, the first argument must be a reference to the date column in your Calendar or Date table, not a scalar value. Second, the function DATE is used to create a date value from year, month and day numbers, and thus takes three arguments. DATE(TODAY()) will result in a syntax error.

DATESBETWEEN is not equivalent to a where clause in SQL, and in general DAX works quite different from SQL.

The correct filter would be something like FILTER(ALL(Table); Table[StartDate] <= TODAY() && Table[EndDate] >= TODAY())

Wednesday, February 1, 2017 11:26 AM

### All replies

• It depends - do you have a table called Value? I assume [Date1] is a column in table Table as well.

You can combine both filter criteria in one FILTER statement, e.g.

Total:= CALCULATE(

SUM(Table[Value1]) + SUM(Table[Value2]);

FILTER(ALL(Table[Date1]; Table[Date2]);

Table[Date1] = TODAY() && Table[Date2] = TODAY()

)

)

Monday, January 16, 2017 1:31 PM
• I need to calculate value and filter date today between two dates
like this

select sum(nvl(value1,0)) + sum(nvl(value2,0)) + sum(nvl(value3,0)) total,
from table
where sysdate between dt_start and dt_end
group by b.dt_inicio, b.dt_fim;

But i have error:

total:=CALCULATE((SUM(table[value1]) + SUM(table[value2]) + SUM(table[value3])),DATESBETWEEN(DATE(TODAY()),table[start],table[end]))

Error on the first parameter DATESBETWEEN today() i need of SYSDATE

• Edited by Thursday, January 19, 2017 1:40 PM
Thursday, January 19, 2017 1:33 PM
• Hi fit9,

Could you please share the error message for further analysis? Or please post some sample data for testing? The syntax is correct.

>>Error on the first parameter DATESBETWEEN today() i need of SYSDATE

Your SYSDATE is different from TODAY(), right?

Best Regards,
Angelia

MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

Friday, January 20, 2017 1:45 AM
• Mustn't SYSDATE be a column here?

Is the syntax really correct here? Wouldn't (DATE(TODAY()) return a scalar instead of the necessary table- or columnn-expression?

Imke Feldmann TheBIccountant.com

Sunday, January 29, 2017 6:47 AM
• There's two obvious errors in the DATESBETWEEN function here: first, the first argument must be a reference to the date column in your Calendar or Date table, not a scalar value. Second, the function DATE is used to create a date value from year, month and day numbers, and thus takes three arguments. DATE(TODAY()) will result in a syntax error.

DATESBETWEEN is not equivalent to a where clause in SQL, and in general DAX works quite different from SQL.

The correct filter would be something like FILTER(ALL(Table); Table[StartDate] <= TODAY() && Table[EndDate] >= TODAY())

Wednesday, February 1, 2017 11:26 AM