cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
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
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
New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Join THE global Microsoft Power Platform event series.

Join THE global Power Platform event series.

Attend for two days of expert-led learning and innovation on topics like AI and Analytics, powered by Dynamic Communities.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Top Kudoed Authors