cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
KH11NDR Member
Member

Data modelling, map weekly table to Monthly table

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

 

IDProductReporting Week Value
10Bike14/09/2018454
20Car14/09/2018355
30Van14/09/20183245
30Car14/09/20185656
40Bike14/09/2018464
40Bike14/09/20185555
10Bike07/09/2018454
20Car07/09/2018355
30Van07/09/20183245
30Car07/09/20185656
40Bike07/09/20183245
40Bike07/09/20185656
10Bike01/09/2018464
20Car01/09/20185555
30Van01/09/2018454
30Car01/09/20185656
40Bike01/09/2018464
40Bike01/09/20185555

 

Table B

 

The monthly values are crossed tabled in Power BI

 

IDProductReporting DateReporting MonthReporting Year01/01/201802/01/201803/01/201804/01/201805/01/201806/01/201807/01/201808/01/201809/01/201810/01/201811/01/201812/01/2018
10Bike01/09/20188201878998798109120131142153164175186
20Car01/09/2018820183534564330174-9-22-35-48-61
30Van01/09/20188201845-3125-12-49-86-123-160-197-234-271-308
30Car01/09/20188201834-96-6-67-128-189-250-311-372-433-494-555
40Bike01/09/20188201834-161-37-122-207-292-377-462-547-632-717-802
40Bike01/09/20188201833-226-68-177-286-395-504-613-722-831-940-1049

 

 

 

Thanks

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
KH11NDR Member
Member

Re: Data modelling, map weekly table to Monthly table

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.

4 REPLIES 4
Highlighted
Ruksuro Member
Member

Re: Data modelling, map weekly table to Monthly table

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 Member
Member

Re: Data modelling, map weekly table to Monthly table

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.

KH11NDR Member
Member

Re: Data modelling, map weekly table to Monthly table

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

KH11NDR Member
Member

Re: Data modelling, map weekly table to Monthly table

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.