Sum of Prior 6 Months based on two dates in same t...

Anonymous · ‎10-03-2019

I have a table of producton order numbers that includes two dates: production order date and defect detection date. I need to calculate the prior 6 months of defects from the previous 6 months of production orders, for each month in a calendar year. So the attached example below shows just a matrix of the months of production on the rows, and months of defect detection on the columns.

The corresponding result for September would be 3,178 (Apr-Sep). Which is the prior 6 months of production and the number of defects in that same period. So for example in the table for month detected "9" and production order "9", that intersection shows a value of 11...meaning 11 defects detected for production orders created in September.

I have not been able to recreate the results utilizing a DAX calculation. I have tried DATESBETWEEN, DATESINPERIOD. I have a date table, which has two relationships with my production order table. Active with the Production Order date, and inactive with the Defect Detection date. And I have used different combinations of this as well. I'm looking for help on wha the proper calculation would be.

Probably not explaining this well enough, but hopefully this is enough to go on.

	Month of Defect Detection
.
Month of Production	1	2	3	4	5	6	7	8	9	Grand Total
1	21	163	180	112	109	49	96	54	25	809
2		28	102	101	115	40	35	96	49	566
3			16	181	154	77	106	54	204	792
4				15	189	196	235	91	69	795
5					38	175	357	267	92	929
6						40	265	295	119	719
7							21	287	192	500
8								27	197	224
9									11	11

sturlaws · ‎10-05-2019

Hi, @Anonymous,

could you provide a sample report/dataset?

cheers,
S

Anonymous · ‎10-07-2019

I wasn't sure how to attach a table properly, but it boils down to three columns: 1) Production Order Date, 2) Defect Detection Date, 3) Defect Qty

There is also a date table, that is just the full date. I haven't created any additional columns for month, year, etc...

Below is a representation of the data, and expected results on the right hand side corresponding to the highlight areas. For each month, it looks back at the previous 6 months of production time, and detection time, and sums the quantity of defects.