cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
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.

View solution in original post

4 REPLIES 4
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.

View solution in original post

Helpful resources

Announcements
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

Power BI Desktop Update - February 2020

Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors