% increase from last month

[akuttler]

Hello,

I have an expression for total payments :

(Sum({<Account={'2000','2001','2002','2003'}>}Trans))*-1

How would I create a measure for % increase in payments from previous month?

I want this to be rolling dates as I load new data every month. So I want...(total for last month-total for current month)/ Total for last month).

[akuttler]

The Current month total works: Sum({<Account = {'2000','2001','2002','2003'}, TransactionDate = {"$(='>=' & Date(MonthStart(Max(TransactionDate)), 'MM/DD/YYYY') & '<' & Date(MonthStart(Max(TransactionDate), 1), 'MM/DD/YYYY'))"}>}Trans)

So how would I alter it to represent last month total?

the expression below gives me null

Above(Sum({<Account = {'2000','2001','2002','2003'}, TransactionDate = {"$(='>=' & Date(MonthStart(Max(TransactionDate), -1), 'MM/DD/YYYY') & '<' & Date(MonthStart(Max(TransactionDate)), 'MM/DD/YYYY'))"}>}Trans)) - 1

[sunny_talwar]

Let me ask you this? Are you using this in a chart or text box object? If this is in a chart, what all dimensions do you have?

[akuttler]

Text object.

[sunny_talwar]

Hahahaha, try this (without Above())

Sum({<Account = {'2000','2001','2002','2003'}, TransactionDate = {"$(='>=' & Date(MonthStart(Max(TransactionDate)), 'MM/DD/YYYY') & '<' & Date(MonthStart(Max(TransactionDate), 1), 'MM/DD/YYYY'))"}>}Trans)/

Sum({<Account = {'2000','2001','2002','2003'}, TransactionDate = {"$(='>=' & Date(MonthStart(Max(TransactionDate), -1), 'MM/DD/YYYY') & '<' & Date(MonthStart(Max(TransactionDate)), 'MM/DD/YYYY'))"}>}Trans) - 1

[akuttler]

Yes! thank you I tried to do the -1 but it didn't work for some reason. here's the kicker...I have a month year filter...so when i select a month, the current month total updates accordingly, but the previous month total goes to zero...why is that?

[sunny_talwar]

What is the name of your month year field?

[lfholland]

Try this.  This will sum your "Trans" for the month of July since this month is August.  If you wanted June, the number in the first "addmonths" piece would be -3 and the number in the second "addmonths" piece would be -2 and so forth.  For a trailing 12 months, the number in the first "addmonths" piece would be -12 and the number in the second one would stay a -1.  For 12 months before that, -24 and -13, etc. etc.  Hope this helps.

=sum({$<Year={"<=$(=Max(Year))"}, MonthName={">=$(=addmonths(today(), -2)) <=$(=addmonths(today(), -1))"},Account={'2000','2001','2002','2003'}>}Trans)

[akuttler]

its a dimension: 'MonthYear' =Date(MonthStart(TransactionDate), 'MMMM YYYY')

[sunny_talwar · Accepted solution]

Do this in the script to create a new field and then use that as your filter.

Date(MonthStart(TransactionDate), 'MMMM YYYY') as MonthYear

and then use this expression

Sum({<Account = {'2000','2001','2002','2003'}, TransactionDate = {"$(='>=' & Date(MonthStart(Max(TransactionDate)), 'MM/DD/YYYY') & '<' & Date(MonthStart(Max(TransactionDate), 1), 'MM/DD/YYYY'))"}, MonthYear>}Trans)/

Sum({<Account = {'2000','2001','2002','2003'}, TransactionDate = {"$(='>=' & Date(MonthStart(Max(TransactionDate), -1), 'MM/DD/YYYY') & '<' & Date(MonthStart(Max(TransactionDate)), 'MM/DD/YYYY'))"}, MonthYear>}Trans) - 1

[lfholland]

Oh, "MonthName" is our field for Month/Year.  You would need to replace that with your "TransactionDate" field, I believe.