• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Tracking historical costs changes in a home made profitability dashboard

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):

DateSKUPriceFees
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:

SKUCOST
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.

DateSKUPriceFeesCOSTPROFIT
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:

View attachment 67905

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:

DateSKUCOST
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
 
Hi, I have not attached any files, just illustrated at the conceptual level in the tables below. I will try to make a sample database to to better illustrate if the picture above did not come across as clear : /. Sorry
 
Top