Having problem with filter in sum

[#1]

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.

[sunny_talwar]

Try this:

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

[#3]

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.

[sunny_talwar]

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

[#5]

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

[sunny_talwar]

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

Best,

Sunny

[sunny_talwar]

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

[#8]

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

[sunny_talwar]

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