Hi, so I have been working at this all day with no luck. I have a table where Product is produced daily. I need to have a new table created, that summarizes the Total Cases of Product produced each month, then the number of Suspected Rejected product calculated monthly, and the percentage calculated from those two numbers.
Table 1 has the cases produced on a daily basis, the date they're produced, and the date represented as Month and Year in separate columns as well. (As well as other information not relevant to this function)
Table 2 has the date of shipment, where it is recorded if cases are damaged at all on the Date, Month and Year damage occurred.
I need table 3 to create the date of each month (Starting 1/1/2024 through Current date, so for today it would be 4/1/2025), This was my idea, but it doesn't function how I would like it to....
"Test", DATESINPERIOD('Cases Produced'[Date].[Date],DATE(2024,1,1),((YEAR(TODAY())-2024)*12)+MONTH(TODAY()),MONTH),
Then to sum both the Cases produced and damaged cases per month from the other two tables.
The biggest problem I am having is I don't seem to find any concise explanations or functions that work to do exactly what I'm doing, and it seems also that half of the solutions I'm seeing exist in "Transform Data" but I can't get any tables I create to show up in there.
I tried writing a blank query but then again all of the functions I just learned aren't relevant there because it behaves so differently.
I feel like I would have it if you could reference a column of an external table against a column in the current table. Something like below?
"Total Cases Produced", CALCULATE(SUM('Cases Produced'[TOTAL CASES]),FILTER('Cases Produced',[Month] = 'This Table'[Month]))
But that doesn't seem to exist. Then that led me down to creating a variable based on the Month column of the current table, but that brought me to the Transform Data Query editor, which I can't reference my new table from at all.
