7 Replies Latest reply on Aug 6, 2018 6:33 PM by Boreak Silk

# Comparing sales across multiple years based on time period determined by start date and end date in parameter

Hi All,

I’m trying to answer a simple question – how are my sales during a time period, determined
by start and end date in the parameter, compared across multiple years. For
example, when I select start date 1 Jan 2018 and end date 30 Jan 2018, I wanted
to see sales for 1-30 Jan in 2018, 2017, 2016, …

I created below formula and it works if the start date and end dates are in the same
year.

SUM(IF (MONTH([Date])*100 + DAY([Date]))>= (MONTH([Start date])*100 + DAY([Start date]))

AND

(MONTH([Date])*100 + DAY([Date])) <=(MONTH([End date])*100 + DAY([End date]))

THEN [Sales]

END)

But if start and end dates are in different years, it does not work. Any idea how
to modify this formula to give the right answer if the start date and end date
are in different years? For example, something that works when I select 30 Dec
2017 and 30 Jan 2018? Please find attached workbook.

Thank in advance for any assistance.

Kind regards

• ###### 1. Re: Comparing sales across multiple years based on time period determined by start date and end date in parameter

Hi Boreak,

Please check this:

BR,

bharat

• ###### 2. Re: Comparing sales across multiple years based on time period determined by start date and end date in parameter

Hi bharat,

Thank for your response but it is not what I'm looking for. I wanted to compare sales across 10-20 financial years.

Cheers

Boreak

• ###### 3. Re: Comparing sales across multiple years based on time period determined by start date and end date in parameter

Hi Boreak,

I believe that comparing two year sales or 10 year sales or 20 year sales the logic would remain same.

I suggest you to please check these:

BR,

bharat

• ###### 4. Re: Comparing sales across multiple years based on time period determined by start date and end date in parameter

Hi,

I suggest to double check your logic or rephrase your brief. You want to have a yearly comparison but for periods start and end at different years. It looks strange. However it is possible.

([Date] >=

{ FIXED YEAR([Date]):MIN(

IF DATEDIFF('year', [Date], [Start date]) >= 1

AND MONTH([Date]) >= MONTH([Start date])

AND DAY([Date]) >= DAY([Start date])

THEN [Date]

END

)}

AND

[Date] <=

{ FIXED YEAR([Date]):MAX(

IF DATEDIFF('year', [Date], [Start date]) >= 1

AND MONTH([Date]) >= MONTH([Start date])

AND DAY([Date]) >= DAY([Start date])

THEN [Date]

END

)})

OR

([Date] >=

{ FIXED YEAR([Date]):MIN(

IF DATEDIFF('year', [Date], [End date]) >= 1

AND MONTH([Date]) <= MONTH([End date])

AND DAY([Date]) <= DAY([End date])

THEN [Date]

END

)}

AND

[Date] <=

{ FIXED YEAR([Date]):MAX(

IF DATEDIFF('year', [Date], [End date]) >= 1

AND MONTH([Date]) <= MONTH([End date])

AND DAY([Date]) <= DAY([End date])

THEN [Date]

END

)})

Please find updated worksheet attached.

Trust this helps.

D

• ###### 5. Re: Comparing sales across multiple years based on time period determined by start date and end date in parameter

Hi Boreak,

Didu get a chance to look at the links i shared earlier?

BR,

bharat

• ###### 6. Re: Comparing sales across multiple years based on time period determined by start date and end date in parameter

Hi,

They are not the case. There is no consideration of cross date parts periods.

D

• ###### 7. Re: Comparing sales across multiple years based on time period determined by start date and end date in parameter

Hi David and Bharat,

Thanks for your help and David your solution is brilliant.

But I still need to work on the current year because the formula does not pick current/selected year.

Cheers

B