Use freshest cost to date available for margin calculation, per day per site per product

iyqp93 — Tue, 27 Apr 2021 12:41:38 GMT

Hello,

I am trying to estimate margin (using Current Cost of Sales accounting logic) based on my proceeds and cost tables, using always the latest best cost info available at the given point in time. I was looking for solutions, but they either give me always the latest cost available (no ok for historical calc.), or I am warned against (EARLIER: lots of computing power if used on big tables; FILTER: used on Data tables instead of reference tables). I operate this currently in Excel (per month, maxifs), and it is very slow, so I am looking for optimum performance, grateful for advice.

Deliveries table. Costs, per day, per site, per product, per depot. Obviously lots of deliveries, so usually I would have a delivery per day per depot. (Meaning: if I know the depot that was used last to supply my site, I can pick up the freshest cost info for that depot.)

- I have day/site/product combinations when there is no delivery (magenta in example)

- I have combinations when there are multiple deliveries – two depots (green in example)

- I have combinations where a site is changing over time from Depot1 to Depot2 (red in example)

- cca ~30.000 delivery combinations/month (this includes depot layer as well)

Sales table. Proceeds and quantities sold, per day, per site, per product.

For each combination (~30-40k rows per month), need to know freshest cost info until any given day to enable margin calculation.

1. last delivery to the site, for the given product, until the given day

2. the depot used for this delivery (largest delivery in case more deliveries per day) - "which depot supplied the site last?"

3. the freshest cost information available for the given depot, for (or closest up to) the given day

I have reference tables for site, day, products, depots. The two data tables (sales and deliveries) are both linked to my reference tables.

Table examples below, with results expected.

Thank you!

-----------------------------------

Delivery table example: (also used to calculate unit cost)

Posting date;SiteCode;Depot;Product code;Quantity;Total Cost

2021.03.01;11111111;BR1008;123456789;1 040;-31 006

2021.03.05;11111111;BR1007;123456789;5 870;-175 797

2021.03.05;11111111;BR1008;123456789;10 690;-320 149; two deliveries on the same day/site/product

2021.03.03;11111111;BR1008;345678901;5 920;-177 265

2021.03.06;11111111;BR1008;345678901;11 050;-330 875; no deliveries (but sales yes!) for the 4th

2021.03.08;11111111;BR1008;345678901;4 810;-131 711

2021.03.02;22222222;BR1000;123456789;3 590;-100 985

2021.03.01;22222222;BR1000;345678901;6 790;-174 270

2021.03.03;22222222;BR3098;345678901;2 300;-60 648

2021.03.05;22222222;BR3220;345678901;4 710;-125 591; depots changing over time

Sales table example, including results expected

Green: pick depot with the biggest quantity

Magenta: use depot that last sent this product here

Red: depot changes over time

Product code;Selling date;Site;Proceeds;Quantity;RESULT: last biggest delivery to site;RESULT: Depot used for last deliv.;RESULT: freshest cost info to date

123456789;01.03.2021;11111111;21 556;440;1040;BR1008;-29,81

123456789;04.03.2021;11111111;116 596;2380;1040;BR1008;-29,81

123456789;05.03.2021;11111111;78 874;1610;10690;BR1008;-29,94

345678901;04.03.2021;11111111;14 697;300;5920;BR1008;-29,94

123456789;02.03.2021;22222222;263 724;5530;3590;BR1000;-28,1

123456789;07.03.2021;22222222;96 821;2030;3590;BR1000;-28,1

345678901;01.03.2021;22222222;344 551;7150;6790;BR1000;-25,6

345678901;04.03.2021;22222222;216 339;4480;2300;BR3098;-26,3

345678901;05.03.2021;22222222;201 852;4180;4710;BR3220;-26,6

Re: Use freshest cost to date available for margin calculation, per day per site per product

daxer-almighty — Wed, 28 Apr 2021 08:20:17 GMT

Hi iyqp93

Looking at this... I don't think it's workable here on the forum. At least not with the current description. Since one can't see the live data and have a clear understanding of the model and relationships, I think it will be very difficult to give you any type of advice. But might be wrong. Maybe it's just me no being able to fully grasp your problem. Sorry.

topic Use freshest cost to date available for margin calculation, per day per site per product in DAX Commands and Tips

Use freshest cost to date available for margin calculation, per day per site per product

Re: Use freshest cost to date available for margin calculation, per day per site per product