2 Replies Latest reply on Jun 15, 2016 6:12 AM by Tom W

    How to calculate Last N days with respective System date(using Today())

    namrata patil

      Hi All,

       

      Using Superstore data, i am using Region, Sales and Order Date dimension and measures.

      I have created 1 parameter "last N days".

      Need to display sales value for all regions by calculating Today()-last N days.

      Suppose, Today() = 15th June 2016 and last N days = 3

      then output should be 15-3=12 i.e Sales data for all Region from 12th June 2016 to 15th June 2016.

      The above output is possible.

      When i insert "last N days" = 20 (anything greater than today's day) then blank and even if last n days = 15 still blank.

      Therefore can you please guide how can :

      Case 1: Today()= 15th June 2016

                     last N days=15

      then Output should be Sales value for all Regions for 15h June 2016

       

      Case 2: Today()= 15th June 2016

                     last N days=20(greater than current date)

      then output should be Sales value for all Regions from 26th May 2016 to 15th June 2016

       

      Case 3: Today()= 15th June 2016

                     last N days = 3 (less than current date)

      then output should be Sales value for all Regions from 12th June 2016 to 15th June 2016.

       

       

      Thanks.

        • 1. Re: How to calculate Last N days with respective System date(using Today())
          Tina Hauser

          Hi Namrata!

          This can be done with a couple of calculated fields and a binary flag. You have the right idea with a parameter. Please see the attached.

          1. Create Parameter: Last N days, and Show Parameter Control

          2. Calculate Dimension: Today

          3. Calculate Dimension: Today - N

          4: Calculate Measure: Include order date flag (this determines if each Order Date is greater than or equal to (Today - N)

          5. Filter for Include order date flag = True

           

          Let me know if you have questions.

          • 2. Re: How to calculate Last N days with respective System date(using Today())
            Tom W

            You could do this more than one way, here's a couple of methods;

            Create a parameter for your number of days called paramDayCount, set it to your N.

             

            Method 1

            • Create a calculated field called 'IsLastN' with the formula [SaleDate] >= dateadd('day',paramDayCount,Today()) and [SaleDate] <= Today()
            • Drag the field to your filters and set it to True.
            • This will only leave Sale Dates between your N

             

            Method 2

            • Create a calculated field called LastNSale with the formula SUM(IF [SaleDate] >= dateadd('day',paramDayCount,Today()) and [SaleDate] <= Today() then SALES end )