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

[Anonymous]

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

[sturlaws · Accepted solution]

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 · Accepted solution]

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]

@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 · Accepted solution]

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

[Anonymous]

@sturlaws 

Thanks Sturla, that did the trick . . . . well, sort of in that it gave me the overall total for the selected project.  The requirement I've got is that management want to see how the costs are progressing, creating a Cumulative/S-Curve report.  What I'm getting is a flat-line showing the overall total all the way across.  The data I have is a month by month spend and, in Excel, I can generate a graph that shows how the overall spend is increasing each month, using a formula to increment the overall cost. as below

However, in Power BI, using the DAX code you've helpfully provided to get the cumulative total, but otherwise the same data I end up with a flat line at £7,121.35

Apologies if I've missed something critical, the date is not heirarchical and the Cumulative SAP Actuals is from the DAX code.  What am I doing wrong ?

[sturlaws]

I am not able to recreate your issue, so pardon me if I now ask a lot of stupid questions in order to identify the problem. Is it possible that you have created a calculated column instead of a measure?

[Anonymous]

No, definitely a Measure

[Anonymous]

@sturlaws 

Hi Sturla, my bad, I just checked and realised I'd put a .[Date] suffix in the final statement.  With that removed it gave me the "curve" I was looking for, now all I need do is stop it looking "blocky" and more "curved"

Thanks so much for your help in this

Regards

Fred