AddMonths function to vStartDate - selecting arbitrary date ranges

[#1]

Hi All

I've been asked to create an app for revenue reporting that allows the end user to input the data ranges the user wants to view - I watched the "Selecting Arbitrary date ranges" video on YouTube, and added the functionality to the app.  I was extremely happy with this, and so was the chap who requested the app in the first place - but now he's asked for a prior period comparative which shows the previous year to the arbitrary date ranges selected.

The formula to show the revenue for the period selected by the user I have created is this:

=Sum({$<Month_YYYY={">=vStartDate<=vEndDate}>}Revenue)

Having had a think about it, I think AddMonths is the right way to go with this, so I came up with this formula:

=Sum({$<Month_YYYY={">=(date(addmonths(Date#('$(vStartDate)','MMM-YYYY'),-12),'MMM-YYYY')<=(date(addmonths(Date#('$(vEndDate)','MMM-YYYY'),-121),'MMM-YYYY')"}>}Revenue)

What I'm expecting to see is if the user selected Jan-2015 as the start date and May-2015 as the end date, the sum of Revenue for Jan-2014 to May-2014.

Depressingly though, I'm seeing the exact same value as for the first formula.  I've spent the past four hours moving single quotes and double quotes around and adding and removing brackets, and I'm utterly at a loss now.

Can anyone help me figure out where I'm going wrong?

Thanks in advance

Lisa

[sunny_talwar]

Can you try this:

=Sum({$<Month_YYYY={">=$(Date(AddMonths(Date#('$(vStartDate)','MMM-YYYY'), -12), 'MMM-YYYY')<=$(Date(AddMonths(Date#('$(vEndDate)', 'MMM-YYYY'), -12), 'MMM-YYYY')"}>}Revenue)

This won't work if you have Month_YYYY as your dimension. In that case you will need to use Above() or Below() functions based on your sorting.

[#3]

Thanks Sunny - tried it, but no change.  I'm not using Month_YYYY as a dimension, only client names.

[Anonymous]

try like this?

=Sum({$<Month_YYYY={">=$(=date(addmonths(Date#('$(vStartDate)','MMM-YYYY'),-12),'MMM-YYYY')<=$(=date(addmonths(Date#('$(vEndDate)','MMM-YYYY'),-12),'MMM-YYYY')"}>} Revenue)

[#5]

Tried that one too - no change.  Thanks for replying though!  Very much appreciated.

[sunny_talwar]

What about if you remove single quotes from around your variables:

=Sum({$<Month_YYYY={">=$(Date(AddMonths(Date#($(vStartDate),'MMM-YYYY'), -12), 'MMM-YYYY')<=$(Date(AddMonths(Date#($(vEndDate), 'MMM-YYYY'), -12), 'MMM-YYYY')"}>}Revenue)

or removing both the single quote and dollar sign expansion

=Sum({$<Month_YYYY={">=$(Date(AddMonths(Date#(vStartDate,'MMM-YYYY'), -12), 'MMM-YYYY')<=$(Date(AddMonths(Date#(vEndDate, 'MMM-YYYY'), -12), 'MMM-YYYY')"}>}Revenue)

[#7 · Accepted solution]

Okay, so I have a solution to this - but using addmonths in the set analysis wouldn't work, no matter what variation of dollar expansion, single quotes or double quotes I tried!

In the end, I set two additional variables - based off my vStartDate and vEndDate, and applied the addmonths function to them: =addmonths('$(vStartDate)', -12)

Works perfectly.  Thanks Sunny and Balraj for your help though.