cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
MitieFred Member
Member

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
 
2 ACCEPTED SOLUTIONS

Accepted Solutions
sturlaws New Contributor
New Contributor

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

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

View solution in original post

sturlaws New Contributor
New Contributor

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

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

View solution in original post

7 REPLIES 7
sturlaws New Contributor
New Contributor

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

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

View solution in original post

MitieFred Member
Member

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

@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 New Contributor
New Contributor

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

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

View solution in original post

MitieFred Member
Member

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

@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 belowExcel Cumulative Capture.PNG

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

Power BI Cumulative Capture.PNG

 

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 New Contributor
New Contributor

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

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?

MitieFred Member
Member

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

No, definitely a Measure
Cumulative Measure.PNG

 

MitieFred Member
Member

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

@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

S-Curve Capture.PNG

 

Helpful resources

Announcements
New Topics Started Badges Coming

New Topics Started Badges Coming

We're releasing new versions of the badge that everyone's talking about. ;) Check your inbox for notifications.

MBAS 2020

Save the new date (and location)!

Our business applications community is growing—so we needed a different venue, resulting in a new date and location. See you there!

Difinity Conference

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Top Solution Authors
Top Kudoed Authors (Last 30 Days)