Union of all values from 3 tables similar and relevant quantity based on DateTime

[nikhilec2007]

Hi all,

I have 3 table, called Ticket , Prouction and Sales as below with SKU, Quantity and Month/Date column.

Ticket
SKU	Quantity	Month
A1	10	2023-07
B1	20	2023-07
E1	5	2023-07
A2	30	2023-08
B2	40	2023-08

Production
SKU	Quantity	Date
A1	5	01-07-2023
A1	2	12-07-2023
A1	4	13-07-2023
B1	11	01-07-2023
B1	9	14-07-2023
C1	10	13-07-2023
A2	22	01-08-2023
A2	8	12-08-2023
B2	15	01-08-2023
B2	25	12-08-2023

Sale
SKU	Quantity	Date
B1	11	01-07-2023
D1	8	12-07-2023
A2	22	01-08-2023
A2	8	12-08-2023
B2	15	01-08-2023
B2	25	12-08-2023

My requirement is to summarize all with Ticket, Monthly Production, Till Date Production, Monthly Sales and till date solutoin with Month and Date as Filter.

Monthly refers to sum of Ticket, Production and Sales based on Month selection.

Till Date refers to sum of Production and Sales till selected date in a month like below.

Month Filter: 2023-07	Date Filter: 12-07-2023
Final Output
	Ticket	Monthly Production	Till Date Production	Monthly Sale	Till Date Sale
A1	10	11	7	0	11
B1	20	20	11	11	0
E1	5	0	0	0	0
C1	0	10	0	0	0
D1	0	0	0	8	8

I am facing an issue in displaying all SKU and quantity when I make a relaitonship between 3 tables..

Need your guidance. Thanks in advance.

[lbendlin]

Use a disconnected calendar table for your slicers, and measures to calculate your values.

[nikhilec2007]

Hi, Thanks for the response.

My issue is when I make relationship between 3 table with SKU column and add

SKU

Ticket

Monthly Production

Monthly Sale

in a report from 3 tables, it filters SKU which are common in 3 tables because it doesn't allow One to Many relation with single direction filter between Summarized SKU table and other 3 tables SKU Column.

Summarized SKU table is a union of distinct SKU from all 3 tables. 

Let me know if anything is yet not clear.

Thanks.