Dear all,


I need your help to find sales based on the store opening date. See below:

See image 1

I need to find the comparable sales for YTD and MTD based on the opening date:
Since store 123 opened in 2015 – This is easy, the comparable sales in 2018 are from the beginning of the year
Store 124 & 125 opened during 2017 and for that reason the comparable YTD sales should start to sum only after Comp date (COLUMN E).
Is there a formula that I can use to get that?

See image 2

Thanks very much for your help.

Attached Images

	image 1.png (34.9 KB, 38 views)
	image 2.png (4.6 KB, 37 views)

I have't a clue what this means!?

Anyway, I try:

I prefer to use SUMIFS(), and as this function uses column references and doesn't allow expressions (like [Qty]*[Ext. Price]) instead, some additional columns are needed (you can hide them).
I assume the result table is a defined Table too.

Into source table (Table1), add columns like
Sum = [@Qty]*[@[Ext. Price]]
TY = AND(Iif(YEAR([@[Opening Date]] = YEAR(TODAY()), [@[Comp Date]], [@[Opening Date]])<[@Date],YEAR([@Date])=YEAR(TODAY()))

In result table, the formula for TY YTD sales will be like

An interesting problem.
To clarify things and make things easier for us, could you supply a file with some data, also, could you set up the table that you show in Image 2 in the file and manually calculate the values you expect to see there Using the data you supply)? In addition, could you put a comment in those cells with sales amounts showing the 2 dates you have used for the Sum in each case?

Dear p45cal,

Thanks very much for your reply.
I've been out for a few weeks and just returned and for that reason i'm just replying now.
Please find attached the file as per your request and hopefully you will be able to help me.

Thanks very much

Attached Files

ricardo sousa.xlsx (457.6 KB, 11 views)

Clarification needed.
YTD seems to be dates starting from the beginning of the calendar year to (in your file) Sat 30 June 2018. This is backed up by Week filter in the YTD pivot including 1 to 26.


MTD seems to be different. Rather than starting from the beginning of a calendar month, in your file you have 5 weeks of data starting Sunday 27 May 2018 and ending Saturday 30 June 2018. That includes 5 days in May as well as all of June; this may be a small mistake on your part, but I need to know whether you want just 4/5 weeks' data or data from the begining of the calendar month (1st June 2018, similar logic to YTD).

Hi p45cal

"Clarification needed.
YTD seems to be dates starting from the beginning of the calendar year to (in your file) Sat 30 June 2018. This is backed up by Week filter in the YTD pivot including 1 to 26."

- 2018 for me started on Sunday the 31st December 2017 therefore YTD starts on that date.


"MTD seems to be different. Rather than starting from the beginning of a calendar month, in your file you have 5 weeks of data starting Sunday 27 May 2018 and ending Saturday 30 June 2018. That includes 5 days in May as well as all of June; this may be a small mistake on your part, but I need to know whether you want just 4/5 weeks' data or data from the begining of the calendar month (1st June 2018, similar logic to YTD)."

- each quarter of the year has a period of 13 weeks, the first 2 months have 4 weeks and the last one has a 5 week period.
- each week starts on a Sunday and may not be the first day of the calendar month (in fact the month of June started on Sunday 7th May)

___________

The columns H-I; O-P; V-W are feed by the pivot.

The columns K-L; R-S; V-Z can´t use the same method because I need to compare only the dates which the store opened last year.
As an example store 104 (opened on the 14 April 2017) last year in YTD collumn till week 26 did 236,093 and this year already did 463,037 growing 96.12% but this is not the correct growth because LY the sales are only from week 15 to week 26. in fact I need to have the like for like figures.

Thank you very much for helping
Best
Ricardo

I didn't fully understand your setup, so I designed a sheet to show how I would proceed.

On sheet comparision, you select store and period. after that, you can use autofilter to get sales for compared weeks/months.

It looks like all your date groupings are on weekly base. So I did likely, but in my table, I used ISO week system (you can always edit formulas to change this):
1. The week starts at Monday;
2. The week belongs into year, where it's Thursday belongs to.

I added the rule:
The week belongs into month, where it's Thursday belongs to.

There was the problem with store opening date - there was nowhere said, what it is. In my table, I did go with 1st sales date in your Table1. The dates in comparison table are set active for comparison, when the store was active and the date was earlier than today in both years.

Attached Files

ricardo sousa.zip (459.3 KB, 11 views)

re:" each quarter of the year has a period of 13 weeks, the first 2 months have 4 weeks and the last one has a 5 week period."
In that case I need to know how you number your weeks each year - I can't match it up with any of the standard week numbering conventions. I need this so that I'm sure it'll work properly in future years (I hope to calculate week numbers with formula to determine start dates and do away with the need for the intermediate pivot tables).

My version of comparison table is easily transformed for any setup. The logic of calculations is:
1. The pair of years to be compared are selected;
2. Years to be compared are calculated;
3. For both years, the January 1st, and its weekday number is calculated;
4. For both years, the date of 1st day of 1st week is calculated based in weekday of January 1st;
5. Following same logic as in p.3 and p.4. the dates of last day of last week are calculated (you calculate 1st day of week for next year and distract 1 from result).
6. All dates from 1st day of 1st week to last day of last week for both compared years are calculated. When date is bigger than last day of last week, an empty string is returned instead;
7. Calculate year/quarter/Month/week numbers for both columns. Easiest is with week numbers. You divide the value of (8 - datarange row number) with seven, and return integer part of result. 1st seven dates will get week number 1, next 7 dates week number 2, etc.. The year will be also simple - all dates in one date column belong to one year, all dates in another date column to another year. For quarter and month calculation, you must apply your own rules, like integer part of (14-week number) divided by 13 for quarter number (and when week number is bigger than 52, the quarter number will be 4). With months it is not so easy. The number of weeks may differ for different years (unless you left out some dates when this occurs). With ISO week numbers, a year has 52 or 53 weeks - with your system probably also. You reserve 8 weeks for 2 first months, and 5 weeks for last month. Remains 39 - 40 weeks for 9 months. Neither 39 nor 40 are not dividing exactly with 9 - the result is between 4 and 5. So you must have 4 months with 4 weeks, and additional 3 - 4 months with 5 weeks (the number depends on number of weeks in year) Or you use quarter numbers - when quarter has 13 weeks (a possible exception is 4th quarter), then in every quarter you must have at least one month with 5 weeks. This is most logical way, as a quarter is defined as 3-month period. I'm not even trying to imagine, how you'll compare two months, when one has 4 weeks, and another 5 weeks worth of dates (with my rules, this problem remains too - there is no way avoid this, except by leaving some dates out);
8. Anyway after that remains the last step - you calculate amounts and quantities for dates in compared years from your source table.

OP must've lost interest.