Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
Try this:
Sum ({$<[Invoice date]={"(=MonthStart([Invoice date]) = '4/20/2015')"}>}[Total Charge amount])
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.
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
Hi, yes, I saw that in your first post and changed it to '4/10/2015' but it still returns no data.
Is there any way for you to share your application so that I can have a look at it?
Best,
Sunny
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
Thanks for your continued help but unfortunately it still doesn't give a result.
Damn it ... Would you be able to share a sample to look at it?