4 Replies Latest reply on Aug 28, 2018 10:00 AM by vengadesh palani

# Current vs Previous N Period to Date AVG Comparison

Hi All

i want to compare the current Period value with Previous N Periods Avg Value  based on period selection (YTD, QTD, MTD, WTD)

Ex:

Period = Month

Previous N Periods = 2

Date Selection = 12/02/2015

so Current Month to Date value is 8

DateValue
12/01/20155
12/02/20153

Previous 2 Periods Avg --> (10+8)/2 --> 9

DateValue
11/01/20154
11/02/20156
10/01/20153
10/02/20155

Reference URL : Current vs Previous Period to Date Comparison

this reference does not have Previous N Periods Avg value

How can i achieve this Logic?

Thanks

• ###### 1. Re: Current vs Previous N Period to Date AVG Comparison

What you are asking for is going to take a lot of steps.  Let's start with the first step first:

I do stuff like this by creating a [Begin Period] calc and an [End Period] calc.

In your example, the values you would set for the two calcs would be 12/1/2015 and 12/2/2015 respectively.  You would get that by setting the [End Period] calc to the parameter setting you have for Date Selection.

Next you would set the [Begin Period] calc by doing a DATETRUNC on the parameter value.

Now here's the thing.  Change your parameter for [Period] so that the internal values are all lowercase.  You can leave the displayed values uppercased as you already have them, but you want the internal value to be lowercase.  The reason is this:  The DATETRUNC takes two arguments:  'date part', and [Your Date].

(You can find date functions here:  Date Functions  )

The date part uses a lower case token for things like 'quarter', 'month', etc.  If your param already has them lowercased for the internal value, you can just use your param for that argument:

DATETRUNC([Period], [Date Selection])

If the user selected Month for the Period, Datetrunc would return a value of the first of the month in [Date Selection].  If the user selected Quarter, then Datetrunc would give you the first day of the quarter in which [Date Selection] falls.

See if you can get that to work first.

(PS:  A sample workbook with sample data would help me show you things.  Can you upload a workbook and data set we can use here?)

• ###### 2. Re: Current vs Previous N Period to Date AVG Comparison

Exactly i need a result like below image its working only for Month selection

• ###### 3. Re: Current vs Previous N Period to Date AVG Comparison

See attached.

I made the [Start Date] and [End Date] calcs I suggested above.

Then I made a calc that tells me the beginning period date for every row.  So if the date value in a row is May 10, and the user selects "month", the beginning period date for May 10 is May 1.  For "quarter" it's April 1.  For "year" it's Jan 1.  Etc.  You can see that on SHeet 8.

I also created a calc called Days to Grab.  If the user selects 11/2/2015 and MONTH, the days to grab is 2.  If QUARTER it's 33.  Etc.

Then I made a calc called [Get this date?].  It uses [Begin Date for this Period] and [Days to Grab] and Start Date and End Date to determine if the given row should be grabbed.  All these are on Sheet 8.  Notice that I don't have any filters on this.

But then you can use [Get this date] as a filter, and you'll only get the rows you need to get.

On sheet 9 I showed a way you can use these calcs.

• ###### 4. Re: Current vs Previous N Period to Date AVG Comparison

Thanks Joe. It's Working fine.