Count complaints received by month, of those, how many were produced last 3 months?

[smithkj1]

I have a table which contains conumser complaints. the table has complaint received date and this is linked to the date when the product was made - [production date]. 

I need to calculate the number of complaints received by month  and of those recieved in a given month, I need to calculate how many of the products were produced in the last 3 months. For example, I need complaints recieved in January, and of those complaints a count of the products which were produced in November, December and January. E.g complaints received in January might be 50, and of those 50, 30 might have been produced in Nov, Dec, January. 

In addition to the data table, i have created a calendar table. 

I'm going around in circles! testing dateadd, datesbewteen, datesinperiod - but I'm getting nowhere. 

I need this analysis for last 4 years. 

Many thanks in advance if you cna help! 

[SaumyaShetty]

Please post a workbook with some dummy data so that people can help you out.

[smithkj1]

HI - thanks so much for your reply - I don't get an option to insert a file. I hope this is enough?

Internal Case Id	Initial Receipt Date	Complaint Metric	Manufacturing Date	Manufacturing Line
1	2/2/2018	1	10/24/2017	1
2	2/2/2018	1	12/21/2017	1
3	2/2/2018	1	12/7/2017	1
4	2/3/2018	1	12/5/2017	1
5	2/3/2018	1	1/24/2018	1
6	2/3/2018	1	12/3/2017	1
7	2/4/2018	1	1/21/2018	1
8	2/4/2018	1	1/21/2018	1
9	2/4/2018	1	12/5/2017	1
10	2/4/2018	1	11/20/2017	2
11	2/4/2018	1	12/28/2017	2
12	2/4/2018	1	7/26/2017	2
13	2/4/2018	1	5/9/2015	2
14	2/5/2018	1	8/17/2017	2
15	2/5/2018	1	1/13/2018	2
16	2/5/2018	1	1/3/2018	2
17	2/5/2018	1	1/3/2018	3
18	2/5/2018	1	12/22/2017	3
19	2/5/2018	1	1/11/2018	3
20	2/5/2018	1	12/17/2017	3

[smithkj1]

so in this picture - the top visual is complaints received date. bottom visual is by manufactured date. 

so when i select complaints received in December (=211), I can see the total productus manufactured in OND was (68+46+11)

I want a table which shows this by month, without having to manually select different filters - i should just see the data by month. 

[hnguy71 · Accepted solution]

I don't have your sample so I can't really test but, an idea would to harvest the min and max dates:

BeginDate = DATE(YEAR(Sheet1[Initial Receipt Date]), MONTH(Sheet1[Initial Receipt Date]) -3, "1")

EndDate = EOMONTH(Sheet1[Initial Receipt Date], -1)

From there, most likely you can use datesinbetween to get the count:

Count Complaints= 
var var_Start = MAX(Sheet1[BeginDate])
var var_End = MAX(Sheet1[EndDate])
Return
CALCULATE(COUNT(Sheet1[Complaint Metric]), DATESBETWEEN(DateTable[Date], var_Start, var_End))

[smithkj1]

Hi - i have another question. I have a measure which calcs complaints received and I can show this data in a matrix, per month. 

I use the datesbetween to calculate the number of complaints produced last 3 months.  I have a calendar table - I have an active relationship with complaint receipt date and an inactive relationship with production date.

Count Complaints L3M =

var var_Start = MAX('Data'[BeginDate])

var var_End = MAX('Data'[EndDate])

Return

CALCULATE(sum('Data'[Complaint Metric]), USERELATIONSHIP('Calendar'[Date], 'Data'[ProductionDate]),DATESBETWEEN('Calendar'[Date], var_Start, var_End))

this is what I need to calculate - for a given month, the number of complaints received (no problem - the first measure gives me that) but of those received, how many were produced in the 3 months previously. 

For example, I need to see on a table, by month, the number of complaints received, and then by month, Of those complaints received in that month, how many were produced in the 3 months previously. so for January, I have a total complaints received in January and of those received in january, how many were produced in Nov, Dec, January. 

So received in January might be 200, Produced last 3 months might be 150 (meaning of 200 complaints recieved, 150 were produced in Nov, Dec, Jan). What my current measure gives me is the total produced in Nov, Dec, Jan - i.e. doesn't take into account whether the complaint was received in january or not. 

I hope it makes sense!! 

thanks in advance

[smithkj1]

thanks so much - in Begindate - I used -2 rather than -3 (this gave me the date range I needed for each month).

otherwise, this works perfectly. I now have the data I need. Thanks so much for your help!!