2 Replies Latest reply on Apr 7, 2017 10:49 AM by Sam OZOBU

# Current Date period(number of record) VS Previous Date period(number of record), depending on user selected parameter (startdate and enddate)

Hi,

I am looking for a solution for a project am working on from the available videos but I haven’t seen any yet.

I have workbook of current year count and want to have another workbook that will show previous year or any time period count depending on the parameter the user select, and am having some difficulty on that.

1. E.g

Total count current year (number of record)  of 2015 = 1000

Then Total count previous year(number of record) of 2014 = 800

And I want this drive by a parameter startdate and enddate.

Parameter e.g

If user select 1/1/2015 – 12/31/2015 then previous count will be whole of 2014

But if 1/1/2015 – 03/31/2015 then previous count will be 1/1/2014 – 03/31/2014

I already have current year but to get the previous year is not coming up right.

Thanks in anticipation

• ###### 1. Re: Current Date period(number of record) VS Previous Date period(number of record), depending on user selected parameter (startdate and enddate)

Hi Sam,

The simple way to achieve this would be to create 2 date parameters.

One we can name "on or before" and the other we can name "on or after"

Create 2 identical sheets with your sum(Number of Records)

Create two calculations, one will filter each of our sheets.

Calculation 1:

[Date field] >= [On or after]

AND

[Date field] <= [On or before]

Calculation 2:

[Date field] >= dateadd('year',-1,[On or after)

AND

[Date field] <= dateadd('year',-1,[On or before)

if you apply calculation 1 to sheet 1, and calculation 2 to sheet 2, checking the 'True' box for the filter options, you should achieve your result.

Happy to talk through any additional questions you have.

Galen

1 of 1 people found this helpful
• ###### 2. Re: Current Date period(number of record) VS Previous Date period(number of record), depending on user selected parameter (startdate and enddate)

Thanks a lot that was helpful.