Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
ID | Product | Reporting Week | Value |
10 | Bike | 14/09/2018 | 454 |
20 | Car | 14/09/2018 | 355 |
30 | Van | 14/09/2018 | 3245 |
30 | Car | 14/09/2018 | 5656 |
40 | Bike | 14/09/2018 | 464 |
40 | Bike | 14/09/2018 | 5555 |
10 | Bike | 07/09/2018 | 454 |
20 | Car | 07/09/2018 | 355 |
30 | Van | 07/09/2018 | 3245 |
30 | Car | 07/09/2018 | 5656 |
40 | Bike | 07/09/2018 | 3245 |
40 | Bike | 07/09/2018 | 5656 |
10 | Bike | 01/09/2018 | 464 |
20 | Car | 01/09/2018 | 5555 |
30 | Van | 01/09/2018 | 454 |
30 | Car | 01/09/2018 | 5656 |
40 | Bike | 01/09/2018 | 464 |
40 | Bike | 01/09/2018 | 5555 |
Table B
The monthly values are crossed tabled in Power BI
ID | Product | Reporting Date | Reporting Month | Reporting Year | 01/01/2018 | 02/01/2018 | 03/01/2018 | 04/01/2018 | 05/01/2018 | 06/01/2018 | 07/01/2018 | 08/01/2018 | 09/01/2018 | 10/01/2018 | 11/01/2018 | 12/01/2018 |
10 | Bike | 01/09/2018 | 8 | 2018 | 78 | 99 | 87 | 98 | 109 | 120 | 131 | 142 | 153 | 164 | 175 | 186 |
20 | Car | 01/09/2018 | 8 | 2018 | 35 | 34 | 56 | 43 | 30 | 17 | 4 | -9 | -22 | -35 | -48 | -61 |
30 | Van | 01/09/2018 | 8 | 2018 | 45 | -31 | 25 | -12 | -49 | -86 | -123 | -160 | -197 | -234 | -271 | -308 |
30 | Car | 01/09/2018 | 8 | 2018 | 34 | -96 | -6 | -67 | -128 | -189 | -250 | -311 | -372 | -433 | -494 | -555 |
40 | Bike | 01/09/2018 | 8 | 2018 | 34 | -161 | -37 | -122 | -207 | -292 | -377 | -462 | -547 | -632 | -717 | -802 |
40 | Bike | 01/09/2018 | 8 | 2018 | 33 | -226 | -68 | -177 | -286 | -395 | -504 | -613 | -722 | -831 | -940 | -1049 |
Thanks
Solved! Go to Solution.
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.
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.
Hang, on I've forgot an important part to the tables on why it's harder than normal.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
94 | |
81 | |
66 | |
58 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |