Sum the "Amount" column only if "Date" column is NOT blank

[PaulMac]

As the title states i need to sum the "Amount" column but only if there is a date in the "Date" column.

I am trying to create a new DAX measure but have hit a roadblock.

I currently have:

=SUM(Table[Amount Column])

I am still a complete novice with DAX and am starting to get frustrated as this seems like such a simple task.

I look forward to any help you are willing to give in the hopes of finding a solution to my problem.

[MFelix]

Hi @PaulMac,

When you are refering to make the sum of the Amount, what do you mean exactly, you want to return  the value of the Amount if there is no date, or do you want to sum the whole column values when the date is not null.

Based on the dax formula you have you should use a calculated measure:

Measure =
CALCULATE (
    SUM ( Table1[Amount] );
    FILTER ( ALL ( Table1[Date] ); Table1[Date] <> BLANK () )
)

Regards,

MFelix

[PaulMac]

Sorry @MFelix but i get this error

This formula is invalid or incomplete: 'The expression is not valid or appears to be incomplete. Please review and correct the expression.
The syntax for ')' is incorrect. (DAX(

=CALCULATE (
              SUM( All_Complaints[Amount of Compensation Paid (£)] ),
              FILTER ( All ( All_Complaints[Date Compensation Was Paid] ), All_Complaints[Date Compensation was Paid]) <> BLANK () )
)

Any thoughts on how I can proceed?

[MFelix]

Hi @PaulMac,

Remove the last  ) from your formula, you have to many brackets.

While copy pasting something went wrong and you have  the addtional one 

Regards,

MFelix

[PaulMac]

Hi @MFelix

I removed the last bracket as per your advice but now I get the following error:

This formula is invalid or incomplete: 'Calculation error in measure 'All_Complaints'[Compensation Paid]: A function 'FILTER' has been used in a True/False expression that is used as a table filter expression. This is not allowed.'.

Any other ideas?

[v-jiascu-msft]

Hi @PaulMac,

Could you please mark the proper answers as solutions?

Best Regards,

Dale

[MFelix · Accepted solution]

Hi @PaulMac,

The issue is in the middle of your measure the measure should look like this

CALCULATE (
    SUM ( All_Complaints[Amount of Compensation Paid (£)] ),
    FILTER (
        ALL ( All_Complaints[Date Compensation Was Paid] ),
        All_Complaints[Date Compensation was Paid] <> BLANK ()
    )
)

Previous measure marked in bold the incorrect bracket:

=CALCULATE (
              SUM( All_Complaints[Amount of Compensation Paid (£)] ),
              FILTER ( All ( All_Complaints[Date Compensation Was Paid] ), All_Complaints[Date Compensation was Paid]
)  <-this is the incorrect bracket

 <> BLANK () )
)

Regards

MFelix

[PaulMac]

Thanks for the reply @MFelix but I get the error message

The syntax for ';' is incorrect. 

I subbed the ; for , but still get an error

I am doing this on an Excel Pivot Table and not I Power BI if that makes any difference.

[MFelix]

Hi @PaulMac,

That as to do with regional settings, replace the ";" by "," on the measure.

Regards,

MFelix

[PaulMac]

Hmmmm, I did substitue the ; for , but still get an error unfortunately

As I mentioned, I doing this measure on a pivot table in Excel instead of Power BI if that makes any difference.

[MFelix]

The formula is the same for both instances

[PaulMac]

Hi @MFelix

Thanks for your reply.

Yes, I want to sum the whole column values when the date is not null.

I have amended my first post as I am working with DAX and not M as I originally stated, see original post for the measure I have so far

Thanks

Paul

[MFelix]

Just adjust my response based on your adjustment of the first post.

Regards,

MFelix