Calculate YTD and QTD

[Pearl_022]

HI All,
Need help in creating formulas for QTD & YTD based on a criteria.


Need a formula for calculating QTD & YTD in below output table based on data table for both TotalBatches and TotalRejection.

Please help me creating calculation.

Data Table
(In my data table I have 12 months of data with multiple same dates)

Site	Date	Month	Year	Totalbatches	TotalRejection
Texas	1/1/2019	1	2019	4	0
Boston	1/1/2019	1	2019	198	4
Chicago	2/1/2019	2	2019	90	7
New Jersey	2/1/2019	2	2019	254	5
New York	3/1/2019	3	2019	567	3
Philadelphia	3/1/2019	3	2019	87	0
Maryland	4/1/2019	4	2019	79	2
Delawar	4/2/2019	4	2019	90	0
Texas	1/1/2020	1	2020	4	0
Boston	1/1/2020	1	2020	3	0
Chicago	2/1/2020	2	2020	7	2
New Jersey	2/1/2020	2	2020	8	3
New York	3/1/2020	3	2020	54	1
Philadelphia	3/1/2020	3	2020	60	5

Thanks in advance.

 

[smalik]

Do you need the QTD Total by Year? or it does not matter? Same for YTD.

Also, do you need the sum for QTD in one cell? or is it a running sum after each transaction in the data?

 

[Pearl_022]

Do you need the QTD Total by Year? or it does not matter? Same for YTD.

Also, do you need the sum for QTD in one cell? or is it a running sum after each transaction in the data?

I want to calculate QTD by year and same for YTD. I have updated data where I have only dec 2019 data and whole year data for 2020.

I want sum for QTD

 

[AlanY]

try this

Book1
	A	B	C	D	E	F	G	H	I	J	K
1	Site	Date	Month	Year	Totalbatches	TotalRejection		Year	QTD	Totalbatches	TotalRejection
2	Texas	01/01/2019	1	2019	4	0		2019	1	1200	19
3	Boston	01/01/2019	1	2019	198	4			2	461	13
4	Chicago	02/01/2019	2	2019	90	7			3	908	8
5	New Jersey	02/01/2019	2	2019	254	5			4	169	2
6	New York	03/01/2019	3	2019	567	3			YTD	2738	42
7	Philadelphia	03/01/2019	3	2019	87	0
8	Maryland	04/01/2019	4	2019	79	2
9	Delawar	04/02/2019	4	2019	90	0
10	Texas	01/01/2020	1	2020	4	0
11	Boston	01/01/2020	1	2020	3	0
12	Chicago	02/01/2020	2	2020	7	2
13	New Jersey	02/01/2020	2	2020	8	3
14	New York	03/01/2020	3	2020	54	1
15	Philadelphia	03/01/2020	8	2020	60	5
16	Texas	01/01/2019	5	2019	4	0
17	Boston	01/01/2019	5	2019	198	4
18	Chicago	02/01/2019	6	2019	90	7
19	New Jersey	02/01/2019	8	2019	254	5
20	New York	03/01/2019	9	2019	567	3
21	Philadelphia	03/01/2019	9	2019	87	0
22	Maryland	04/01/2019	11	2019	79	2
23	Delawar	04/02/2019	12	2019	90	0
Sheet1

Cell Formulas
Range	Formula
J2:K5	J2	=SUMPRODUCT(($D$2:$D$23=$H$2)*(ROUNDUP($C$2:$C$23/3,0)=$I2)*(E$2:E$23))
J6:K6	J6	=SUMIF($D$2:$D$23,$H$2,E2:E23)

 

[Pearl_022]

Hi Alan,

Thanks for your response. I am trying but not sure its not working it seems. Lets say I am creating report for only 2020 data, where I need to make my YTD work with site filter in dashboard I created with graphs. How can I achieve that ?


For Example,

	MTD	YTD	QTD	2019 YTD
Total Baches

Thanks,
Pearl

 

[AlanY]

Thanks for your response. I am trying but not sure its not working it seems. Lets say I am creating report for only 2020 data, where I need to make my YTD work with site filter in dashboard I created with graphs. How can I achieve that ?


For Example,

	MTD	YTD	QTD	2019 YTD
Total Baches

Thanks,
Pearl

I might misunderstood your requirements.
based on the data in your first post could you fill in the results expected for post #5 so that we know the expected results.

 

[Pearl_022]

Hi Alan,

I created one dashboard view where I need to calculate MTD, YTD and QTD. I need MTD, YTD and QTD should work with site filter in dashboard. Attached screenshot might give you better understanding. Please let me know if you are still not clear. I need to deliver this dashboard next week.

Another issue is when I calculated YTD and it is giving me result for all sites in dashboard and I am unable to connect slicer/filter for that.

Site	Date	Month	Year	Totalbatches	TotalRejection
Texas	12/1/19​	12​	2019​	4​	0​
Boston	12/1/19​	12​	2019​	5​	4​
Chicago	12/1/19​	12​	2019​	6​	0​
New Jersey	12/1/19​	12​	2019​	1​	4​
Chicago	1/1/20​	1​	2020​	90​	7​
New Jersey	1/1/20​	1​	2020​	6​	5​
New York	1/1/20​	1​	2020​	0​	3​
Philadelphia	2/1/20​	2​	2020​	10​	0​
Maryland	2/1/20​	2​	2020​	15​	2​
Delawar	3/1/20​	3​	2020​	30​	0​
Texas	3/1/20​	3​	2020​	4​	0​
Boston	4/1/20​	4​	2020​	3​	0​
Chicago	4/1/20​	4​	2020​	7​	2​
New Jersey	5/1/20​	5​	2020​	8​	3​
New York	5/1/20​	5​	2020​	30​	1​
Philadelphia	6/1/20​	6​	2020​	15​	5​
Ohio	6/1/20​	6​	2020​	2​	0​
Maryland	6/1/20​	6​	2020​	7​	2​
Delawar	7/1/20​	7​	2020​	5​	0​
Texas	7/1/20​	7​	2020​	4​	0​
Boston	8/1/20​	8​	2020​	3​	0​
Chicago	8/1/20​	8​	2020​	7​	2​
New Jersey	9/1/20​	9​	2020​	8​	3​
New York	9/1/20​	9​	2020​	2​	1​
Philadelphia	9/1/20​	9​	2020​	5​	5​
Ohio	10/1/20​	10​	2020​	10​	0​
Maryland	10/1/20​	10​	2020​	15​	2​
Delawar	11/1/20​	11​	2020​	5​	0​
Texas	11/1/20​	11​	2020​	4​	0​
Boston	12/1/20​	12​	2020​	3​	0​
Chicago	12/1/20​	12​	2020​	7​	2​
New Jersey	12/1/20​	12​	2020​	8​	3​
New York	12/1/20​	12​	2020​	3​	1​
Philadelphia	12/1/20​	12​	2020​	1​	5​