3 Replies Latest reply on Dec 20, 2016 8:11 AM by Lotte Kammenga

# Exclude recent three months

Hi,

I have a dashboard where I need to exclude the recent three months. When someone buys a product, they have a return policy of three months. So the number of transactions is mature up to today, but we we can only display the number of sales mature up to three months. I have two questions.

I have a sales dashboard, that needs to be filtered to exclude the most recent three months. I made this a few months ago and the formula is

month(today())-month([SaleDate])<4

and

year(today())= year([SaleDate])

And then it filters out the true cases. But that's going to fail for the first 3 months of next year. How do I change it to exclude the most recent 3 months in the beginning of the new year?

My second question is similar:

I want to make a transactions dashboard that has a tooltip that, for mature months, says "out of the 500 transactions, 84% led to sales", but if it is in the most recent three months want it to say something like "out of the 500 transactions <not mature> led to sales" or something. As long as it only gives a number for the mature months I'm happy.

So I think I need a formula like

if <month is more than 3 months ago formula> then [% Sales] end

But I'm not sure how to make the <month is more than 3 months ago formula> part because if I use the above one it says that I can't mix aggregate and non aggregate measures. The % sales field is a calculation that is sum([Sales])/sum([Transactions]).

Help on either question is appreciated!

I can't give an example workbook as it contains customer data, sorry.

Thanks,
Lotte

• ###### 1. Re: Exclude recent three months

Hi Lotte,

a. [Order Date] is equivalent to your [Sales Date] column

b. [Latest Sales On] is a parameter that I used. This was created to test the scenarios where current month is Jan or Feb of a year.

c. The below condition excludes recent three months.

[Order Date] < DATEADD('month',-2,DATETRUNC('month',[Latest Sales On]))

Let me know if it helps.

Best,

Sujay

1 of 1 people found this helpful
• ###### 2. Re: Exclude recent three months

hi Lotte,

So one way to do this (and I use this for everything!) is to create a date index. This creates a dimension, in which the last date (be that day, week, month...etc) is 0, and then the previous one is -1, then -2...and so on.

[Month Index]

DATEDIFF('month', today(), [SaleDate])*-1

If you want this to be rolling, you could use DATEDIFF('day', today(), [SaleDate])*-1 and in this case you'd be filtering to 0 to -90

Once you have this you can create a boolean

[exclude last 3 months]

[Month Index] <-3

and then use this in your filters, or the other formula you detailed.

Hope this helps, does the trick and makes sense...if any of these questions is a no, let me know (and I'm sure we can adapt it for your needs)

2 of 2 people found this helpful
• ###### 3. Re: Exclude recent three months

Thank you!