mikaelaleksandrowicz
New Member
- Joined
- Jun 25, 2022
- Messages
- 2
- Office Version
- 2019
- Platform
- Windows
Dear Friends,
I have a profitability tracking solution for my small online business that I have build over the years using first Excel and then PowerQuerry & PowerPivot (as the datasets grew too big). I get transactions reports from the sales platform every month in txt files, I load them up to PowerQuerry (load txt from a directory). Works well. The transaction report has Sales, SKU, Date, Fees columns (I am simplifying it here a lot just to get the point across easily):
Then I a separate sheet (that I load up with Power Query to a separate table) I have cost data per product:
Then I do merge both tables(querries) in PowerQuerry in order to have the profit metric (Price-COST), that I later on use in pivot tables, charts, dashboard. I merge on SKU, cost is added to the sales table. Profit is a new calculated column.
The model is of course much more complex with many relationships defined and multiple tables (cost components, markets, etc), this is how it looks, just to illustrate:
The biggest issue with my solution is that I don’t track historical COST per SKU, the cost table has recent cost only. if the supplier raises prices this month, the my profit data for last month and the past would be affected. The cost is merged with the sales data at current cost always. Back 2 years ago I didn’t have a plan how to implement this so I went along the easy, focusing my portability monitoring on current periods only. However, now as we grow I feel it is a must to have it updated to track and compare historical data. I plan to start tracking the cost in this way, to implement date into the table:
However, I come across a hurdle of not knowing how to match COST to the SALES data considering changing cost. Looks to me that merging the queries won’t work. Since I merge on SKU, there is no way to take date into account. Cost increases happen on a random basis, sales happen every day. I was thinking that some sort lookup with time frame and SKU would work maybe? What would be the best way to handle my problem. Would some sort of DAX calculated column in PowerPivot do? Or something in the PowerQuerry?
Looking forward to your reply!
Michal – self-thought beginning power BI enthusiast
I have a profitability tracking solution for my small online business that I have build over the years using first Excel and then PowerQuerry & PowerPivot (as the datasets grew too big). I get transactions reports from the sales platform every month in txt files, I load them up to PowerQuerry (load txt from a directory). Works well. The transaction report has Sales, SKU, Date, Fees columns (I am simplifying it here a lot just to get the point across easily):
| Date | SKU | Price | Fees |
6/25/2022 | ABC | 10 | 2 |
6/24/2022 | BCD | 9 | 1 |
6/23/2022 | CDE | 8 | 1 |
Then I a separate sheet (that I load up with Power Query to a separate table) I have cost data per product:
| SKU | COST |
| ABC | 3 |
| BCD | 2 |
| CDE | 2 |
Then I do merge both tables(querries) in PowerQuerry in order to have the profit metric (Price-COST), that I later on use in pivot tables, charts, dashboard. I merge on SKU, cost is added to the sales table. Profit is a new calculated column.
| Date | SKU | Price | Fees | COST | PROFIT |
6/25/2022 | ABC | 10 | 2 | 3 | 5 |
6/24/2022 | BCD | 9 | 1 | 2 | 6 |
6/23/2022 | CDE | 8 | 1 | 2 | 5 |
The model is of course much more complex with many relationships defined and multiple tables (cost components, markets, etc), this is how it looks, just to illustrate:
The biggest issue with my solution is that I don’t track historical COST per SKU, the cost table has recent cost only. if the supplier raises prices this month, the my profit data for last month and the past would be affected. The cost is merged with the sales data at current cost always. Back 2 years ago I didn’t have a plan how to implement this so I went along the easy, focusing my portability monitoring on current periods only. However, now as we grow I feel it is a must to have it updated to track and compare historical data. I plan to start tracking the cost in this way, to implement date into the table:
| Date | SKU | COST |
3/17/2022 | ABC | 1 |
5/6/2022 | ABC | 1.5 |
6/24/2022 | ABC | 2 |
3/17/2022 | BCD | 2 |
5/6/2022 | BCD | 2.5 |
6/24/2022 | BCD | 3 |
3/17/2022 | CDE | 2 |
5/6/2022 | CDE | 2.5 |
6/24/2022 | CDE | 3 |
However, I come across a hurdle of not knowing how to match COST to the SALES data considering changing cost. Looks to me that merging the queries won’t work. Since I merge on SKU, there is no way to take date into account. Cost increases happen on a random basis, sales happen every day. I was thinking that some sort lookup with time frame and SKU would work maybe? What would be the best way to handle my problem. Would some sort of DAX calculated column in PowerPivot do? Or something in the PowerQuerry?
Looking forward to your reply!
Michal – self-thought beginning power BI enthusiast
