8 Replies Latest reply: May 13, 2015 1:25 PM by Sunny Talwar

# Having problem with filter in sum

Hi I'm having trouble with setting a filter in a SUM statement.  I'm defining a measure to always return a total amount for a specific month.  I'm trying to use the monthstart function within the measure definition but can't seem to get it right.

If I have the measure defined like this it works:

Sum ({\$<[Invoice date]={"4/20/2015"}>}[Total Charge amount])

But what I really want is to get the charges for the entire month of April so I changed it to this:

Sum ({\$<Monthstart([Invoice date])={"4/1/2015"}>}[Total Charge amount])

But that gives me an error in the expression.  I can get rid of the error by doing this:

Sum ({<\$(Monthstart([Invoice date]))={"4/1/2015"}>}[Total Charge amount])

But that returns no data.

I verified what the values are after Monthstart is applied by modifying the dimension in a table and the values for April all show "4/1/2015"

Thanks in advance for any help.

• ###### Re: Having problem with filter in sum

Try this:

Sum ({\$<[Invoice date]={"(=MonthStart([Invoice date]) = '4/20/2015')"}>}[Total Charge amount])

• ###### Re: Having problem with filter in sum

Thanks for the reply.  I may not have explained what I'm looking for well.  Your solution above looks like it is looking for anything with an Invoice Date of 4/1/2015.  The Invoice dates will vary for example 4/8/2015, 4/20/2015.  What I'm looking to do is have all those invoices come back as part of the sum value.

• ###### Re: Having problem with filter in sum

I understood your requirement, but I think I had a slight typo in the expression, do you mind trying this and seeing if this works:

Sum({\$<[Invoice date]={"(=Date(MonthStart([Invoice date]), 'M/DD/YYYY') = '4/01/2015')"}>}[Total Charge amount])

This expression should Sum the amounts for all April Invoice Dates.

Best,

Sunny

• ###### Re: Having problem with filter in sum

Hi, yes, I saw that in your first post and changed it to '4/10/2015' but it still returns no data.

• ###### Re: Having problem with filter in sum

Is there any way for you to share your application so that I can have a look at it?

Best,

Sunny

• ###### Re: Having problem with filter in sum

And did you do '4/10/2015' or '4/01/2015'? Just want to make sure we are not doing typo mistakes here.

Additionally I added the date format as well, just so if you have a different format in your database it would fix that problem

Sum({\$<[Invoice date]={"(=Date(MonthStart([Invoice date]), 'M/DD/YYYY') = '4/01/2015')"}>}[Total Charge amount])

Try copy pasting the above expression and see if you still don't get a result. If you do great, if not then I would be able to share a sample?

Best,

Sunny

• ###### Re: Having problem with filter in sum

Thanks for your continued help but unfortunately it still doesn't give a result.

• ###### Re: Having problem with filter in sum

Damn it ... Would you be able to share a sample to look at it?