topic Re: Having problem with filter in sum in App Development

Having problem with filter in sum

Wed, 13 May 2015 15:35:08 GMT

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

sunny_talwar — Wed, 13 May 2015 15:38:34 GMT

Try this:

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

Re: Having problem with filter in sum

Wed, 13 May 2015 15:58:43 GMT

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

sunny_talwar — Wed, 13 May 2015 16:03:15 GMT

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

Wed, 13 May 2015 16:40:54 GMT

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

sunny_talwar — Wed, 13 May 2015 16:42:42 GMT

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

sunny_talwar — Wed, 13 May 2015 16:45:03 GMT

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

Wed, 13 May 2015 17:23:26 GMT

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

Re: Having problem with filter in sum

sunny_talwar — Wed, 13 May 2015 17:25:35 GMT

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