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

    Exclude recent three months

    Lotte Kammenga

      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
          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.

           

          Best,

          Sujay

          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