Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

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

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

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

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

Ruksuro
Helper III
Helper III

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.

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.

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.