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())

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())

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())

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 )