locked
DAX Calculate with 2 dates RRS feed

  • 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

Answers

  • 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
    Answerer

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
    Answerer
  • Thank you for answering ...
    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 fit9 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
    Answerer
  • 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
    Answerer