Data modelling, map weekly table to Monthly table

[KH11NDR]

Hi Guys,

I have table A which runs weekly, and table B that runs monthly.

 I need to do weekly calcualations using Monthly figures, how do I do this?  

I'm not gonna say how I did it, as it did work but, It's not 100% and I don't think it's best practice, I want a fresh take on this.

So I need to join or match on ID & Product and then Weekly reporting date to fall in within the Monthly Reporting Date.

Table A

ID	Product	Reporting Week	Value
10	Bike	14/09/2018	454
20	Car	14/09/2018	355
30	Van	14/09/2018	3245
30	Car	14/09/2018	5656
40	Bike	14/09/2018	464
40	Bike	14/09/2018	5555
10	Bike	07/09/2018	454
20	Car	07/09/2018	355
30	Van	07/09/2018	3245
30	Car	07/09/2018	5656
40	Bike	07/09/2018	3245
40	Bike	07/09/2018	5656
10	Bike	01/09/2018	464
20	Car	01/09/2018	5555
30	Van	01/09/2018	454
30	Car	01/09/2018	5656
40	Bike	01/09/2018	464
40	Bike	01/09/2018	5555

Table B

The monthly values are crossed tabled in Power BI

ID	Product	Reporting Date	Reporting Month	Reporting Year	01/01/2018	02/01/2018	03/01/2018	04/01/2018	05/01/2018	06/01/2018	07/01/2018	08/01/2018	09/01/2018	10/01/2018	11/01/2018	12/01/2018
10	Bike	01/09/2018	8	2018	78	99	87	98	109	120	131	142	153	164	175	186
20	Car	01/09/2018	8	2018	35	34	56	43	30	17	4	-9	-22	-35	-48	-61
30	Van	01/09/2018	8	2018	45	-31	25	-12	-49	-86	-123	-160	-197	-234	-271	-308
30	Car	01/09/2018	8	2018	34	-96	-6	-67	-128	-189	-250	-311	-372	-433	-494	-555
40	Bike	01/09/2018	8	2018	34	-161	-37	-122	-207	-292	-377	-462	-547	-632	-717	-802
40	Bike	01/09/2018	8	2018	33	-226	-68	-177	-286	-395	-504	-613	-722	-831	-940	-1049

Thanks

[KH11NDR · Accepted solution]

I've got it working, I had to a 4 field match In original merge and then 5 field match on the third table merged in with the original data and do a full outer join on all merges.

[KH11NDR]

Hang, on I've forgot an important part to the tables on why it's harder than normal.

[Ruksuro]

If this is the same data I would just have two date columns, one with the date and one with monthstart(date).

Best practice would be a date table linked on date but this is not always necessary.

[KH11NDR]

It's a dummy dataset

I did something similar to your suggestion, plus I added a year field, but I'm not getting the results I need, I've tried Append, Merge and Many to Many.

I get one set of values but 0 for the second set.