Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

DAX comparison operations do not support comparing values of type Text with values of type Date.

I am trying to create an S-Curve from our MS Project task data using the following DAX code

Cumulative Actuals =
CALCULATE (
    SUM ( 'Tasks'[ActualCost]),
    FILTER (
        ALL ( 'Tasks' ),
        NOT ('Tasks'[ActualCost] = BLANK() ) &
            'Tasks'[TaskFinishDate].[Date] <= MAX ('Tasks'[TaskFinishDate].[Date])
    )
)
However I am receiving the above message. I've seen a number of solutions (regarding comparing text to numeric etc.), which got me this far, but am now at a loss as to how to get to the final result.
 
The ActualCost field is defined as numeric, with 2 decimal places, however some entries are zero or are blank and I'd like to exclude both from the result.  The TaskFinishDate is defined as Date, not DateTime.
 
The answer, I'm sure, will be obvious once I see it, but I can't figure it out.
 
Thanks for any help you can give
Fred
 
Need Help
  • sturlaws's avatar
    sturlaws
    6 years ago

    Hi,

     

    I think the culprit is your AND-operator, it should be && instead of &

    you could write your code like this

    Cumulative Actuals =
CALCULATE (
    SUM ( 'Tasks'[ActualCost] ),
    FILTER (
        ALL ( 'Tasks' ),
        NOT ( ISBLANK ( 'Tasks'[ActualCost] ) )
            && 'Tasks'[TaskFinishDate] <= MAX ( 'Tasks'[TaskFinishDate] )
    )
)

    Cheers,
    Sturla

  • sturlaws's avatar
    sturlaws
    6 years ago

    Try this

    Cumulative Actuals =
CALCULATE (
    SUM ( 'Tasks'[ActualCost] ),
    FILTER (
        ALLEXCEPT ('Tasks','Tasks'[Project ),
        NOT ( ISBLANK ( 'Tasks'[ActualCost] ) )
            && 'Tasks'[TaskFinishDate] <= MAX ( 'Tasks'[TaskFinishDate] )
    )
)

    If you have other filters you want to keep, they need to be added to the ALLEXCEPT-function

    cheers,
    Sturla

7 Replies

  • sturlaws's avatar
    sturlaws
    Icon for Resident Rockstar rankResident Rockstar
    6 years ago

    Hi,

     

    I think the culprit is your AND-operator, it should be && instead of &

    you could write your code like this

    Cumulative Actuals =
CALCULATE (
    SUM ( 'Tasks'[ActualCost] ),
    FILTER (
        ALL ( 'Tasks' ),
        NOT ( ISBLANK ( 'Tasks'[ActualCost] ) )
            && 'Tasks'[TaskFinishDate] <= MAX ( 'Tasks'[TaskFinishDate] )
    )
)

    Cheers,
    Sturla

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      sturlaws 

      Thanks for the solution, works as requested, unfortunately my request wasn't quite correct as it shows the Actuals for all projects, rather than the one(s) I have filtered (via slicer) or in the Filters.

      What should I add to achieve something other that a >£30m horizontal line ?

       

      Cheers

      Fred

      • sturlaws's avatar
        sturlaws
        Icon for Resident Rockstar rankResident Rockstar
        6 years ago

        Try this

        Cumulative Actuals =
CALCULATE (
    SUM ( 'Tasks'[ActualCost] ),
    FILTER (
        ALLEXCEPT ('Tasks','Tasks'[Project ),
        NOT ( ISBLANK ( 'Tasks'[ActualCost] ) )
            && 'Tasks'[TaskFinishDate] <= MAX ( 'Tasks'[TaskFinishDate] )
    )
)

        If you have other filters you want to keep, they need to be added to the ALLEXCEPT-function

        cheers,
        Sturla