Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
MitieFred
Helper V
Helper V

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
sturlaws
Resident Rockstar
Resident Rockstar

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

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
Resident Rockstar
Resident Rockstar

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 

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

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

@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 ?

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?

No, definitely a Measure
Cumulative Measure.PNG

 

@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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.