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

    Exclude recent three months

    Lotte Kammenga


      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



                               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.


        • 1. Re: Exclude recent three months
          Sujay Paranjpe

          Hi Lotte,


          To answer your first query, please check if the following condition helps. Here,


          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.




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

            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.


            Add the calculated field

            [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