3 Replies Latest reply on Jul 27, 2018 12:36 AM by eva_lesny

# SUM of last 12 months

Hey everyone,

I am looking to SUM the last 12 months worth of data in a specific table. . however there are some oddities. The obvious answer is to just set the filter to only the last 12months, but that would exclude some data that i need. I have 10-15 different partners (dimension) that i need to see the last 12 months of available data for, no matter the date range. Is there a way that i can do this? See example below;

Partner 1:

Data range: 01/01/2017 - Current

Partner 2-

Data range: 01/01/2015 - 05/01/2017

Partner 3-

Data range: 04/2017/2017 - 03/01/2018

As you can see each partner could have a different range of data, and if i set a filter to show only previous 12 months it will exclude some records. I need something that shows last 12 months of data BY PARTNER (dimension).

• ###### 1. Re: SUM of last 12 months

Try this:

Create a calculated field:

Date >= dateadd('month', -12, {Fixed Partner : max(Date) } )

Then put this on the filter and set it to true

• ###### 2. Re: SUM of last 12 months

THANK YOU! I was able to figure it out, however your calculation shows the last 13 months for the given partner adjusted below it shows 12 months;

Date >= dateadd('month', -11, {Fixed Partner : max(Date) } )

• ###### 3. Re: SUM of last 12 months

Ah yes, that should have been either a -11 or just > than instead of >=.