Selected Month vs Previous Month

[#1]

I am trying create an expression which calculated the difference between the selected month's costs and the one preceeding it.

Below is an explanation of my key fields:

• [Charge Rate] - Column with spend I am trying to sum
• [Data Feed Date] - Date formatted as 8/1/2012 12:00 AM
• [Data Feed Month] - Date formatted as Aug 2012

I do not want to be tied to the most recent month (I want to be able to select which month I am viewing). I was able to create a label using the following formula (=AddMonths([Data Feed Month],-1)).

I tried applying this to my expression but it is not working. (sum({<[Data Feed Month]={'=AddMonths([Data Feed Month],-1)'}>}[Charge Rate]))

Any help would be greatly appreciated.

[hallquist_nate]

Have you tried using the function "GetCurrentSelection" or "GetCurrentField"?  I think you need to use GetCurrentSelection, on this. 

Give it a try and let me know...

Nate

[#3]

Getcurrentselection appears to list in words everything that is currently selected (like a current selection box). The problem is this lists everything and I am only concerned about what date I am filtered on and displaying the previous month.

[hallquist_nate]

Take a look at GetFieldSelections.  That might be what you are looking for. 

I also use this formula...

(Sum({$< BookedYear={$(=Max(BookedYear)-1)}>}  (ORDERED_QUANTITY*UNIT_SELLING_PRICE)))

In this case, you have to force the user to select a year, but you can do that by using the Conditional Expression so that the expression won't calculate unless a dimension value is chosen.   I avoided that by setting this tab to select Current year when activated. 

Will this suffice?

[#5 · Accepted solution]

I found the solution from another discussion thread. It appears I needed to format as a date for it to work.

Below is the solution and the explanation from the other thread

(Sum({<[Data Feed Month]={"$(=date(AddMonths([Data Feed Month],0),'MMM YYYY'))"}>} [Charge Rate]))

You need to format the SelDate after you subtract the month. It doesn't preserve your special formatting, and is doing more of a text comparison than a numeric comparison. Also, once you do format it, you're going to have to put it in quotes or QV will probably get confused. So I'm guessing that this would work:

Sum({1<SelDate={"$(=date(AddMonths(SelDate,-1),'MMM YYYY'))"}>} DollarAmount)