Set Analysis issue. Need to create a report that pulls for the current month(June 2016), a list of customers whose last purchase was May 31, 2015 or earlier.

[neena123]

So basically the report needs to go back 12 months to 24 months of the last purchases made by the customer. So the full span from last purchase is June 1, 2013 to May 31, 2015. So, the report would look at all customers in our database and if their last purchase was 12-36 months ago, pulled into a report with no purchases in the current year. I am having difficulty understanding Set Analysis. I have looked at many articles but I can't seem to get my expressions to work. Any thoughts on why my expressions below aren't working?

=sum({$<[Fiscal Month]={"<$(=Month(Today(),-23))"}>}[Sales Amount])

=sum({$<[Fiscal Month]={"<$(=Month(Today(),-11))"}>}[Sales Amount])

[sunny_talwar]

May be this:

=Sum({$<[Fiscal Month]={"<$(=Month(AddMonths(Today(),-23)))"}>}[Sales Amount])

=Sum({$<[Fiscal Month]={"<$(=Month(AddMonths(Today(),-11)))"}>}[Sales Amount])

[neena123]

Thanks for the quick reply! But it did not work.

[#4]

Hi,

Try creating a master calender in your script based on the the min and max date of your date range. From there, incorporate the "year" field in your set analysis. This should allow you to now do rolling 12, 24, etc. calculations. 

Hope this helps. 

Regards,

Pravesh

[neena123]

How is that created though? I do have a calendar but how do I use it to create rolling calculations?

[#6]

the following expression can be used:

sum({<Month={'>=$(=max(Month)-11)<=$(=max(Month))'}>}Amount)