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

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 Helper IV
Helper IV

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.

View solution in original post

4 REPLIES 4
Highlighted
Ruksuro Helper III
Helper III

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 Helper IV
Helper IV

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 Helper IV
Helper IV

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 Helper IV
Helper IV

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.

View solution in original post

Helpful resources

Announcements
Announcing the New Spanish Forum

Announcing the New Spanish Forum

Do you need help in Spanish? Check out our new Spanish community section.

April 2020 Community Highlights

April 2020 Community Highlights

Info on our Super Users, MBAS content and badges, and updates to our support articles. - Read the full Community Highlights.

MBAS Gallery 2020

MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

Top Solution Authors
Top Kudoed Authors