Opening amount

[#1]

Hi All.

I’m hoping you can assist with this basic formula: 

I am trying to total up transactions (AMOUNT) up and until (but not including) the month selected.  

So, if I select 2013 (YEAR), May (MONTH), I would like the column to show the total of AMOUNT from the beginning of time until the end of April 2013.

Any ideas? 
Thanks again

[jvitantonio]

Let's suppose you have a date filed like "MMM-YYYY" (ex: Jan-2013)

Now your formula should look like:

SUM({$<Date = {"<$(=getcurrentselections(Date))"}>} Amount)

[Gysbert_Wassenaar]

You're going to need a year-month field if you don't have a date field already. If you have a date field you can use this:

sum({<YEAR=,MONTH=,MyDate={'<$(=monthstart(max(MyDate)))'}>}AMOUNT)

[#4]

Thanks for the prompt reply Gysbert

I'm working with pieta1230 on this, and have tried your formula above.

I have a PERIODKEY field which I bring through as YEAR & num(MONTH,'00') as PERIODKEY.

When i use your formula, i replace 'MyDate' with 'PERIODKEY' and the result of the selection:

YEAR=2013
MONTH=FEB

brings through all data until the end of 2012, but does not include transactions from Jan 2013..

Any ideas?

[Gysbert_Wassenaar · Accepted solution]

Your PERIODKEY field should preferably be a number. YEAR*12+num(MONTH) or YEAR*100+Month would be better. Then this should work:

sum({<YEAR=,MONTH=,PERIODKEY={'<$(=max(PERIODKEY))'}>}AMOUNT)

But if PERIODKEY is a string than maxstring ought to work too, maybe just a bit slower.

[#6]

Thanks Gysbert - that did the trick!!