Pricing bulk commodity purchases that have volatile prices

DAX DAX Calculations
1

Hello All,

I’m trying to find a DAX-based solution for pricing my bulk fuel purchases on a declining balance basis. As the prices for bulk fuel changes daily, I need to be able to price my sales based on the price that I purchased the fuel at. If I add a $0.25 markup to the fuel that I sell, I must know when the litres those litres have been consumed and the next applicable price would then apply. Previously I had been using averages, but I require more precision in understanding these slim and highly volatile margins.

Scenario:
Empty 200,000 litre fuel tank
Jan 1, 2021 - 64,000 litres purchased at $1.00/litre
Jan 10, 2021 - 50,000 litres purchased at $1.10/litre
Jan 31, 2021 - 60,000 litres purchased at $0.95/litre

My fuel table tracks fuel purchases and prices by date.
My Sales table tracks fuel sales volumes by date

Ay help, much appreciated.
Scott

EDIT: Typo in initial tank capacity

2

Hi @smarta. I’m a bit confused by your scenario: if your tank is 20K, how can you purchase 64K? Perhaps it will become clearer to the forum members if you attach your work-in-progress PBIX file as well as a sample mock-up in Excel to show us your desired outcome?
Greg

3

Hi @smarta , we aim to consistently improve the topics being posted on the forum to help you in getting a strong solution faster. While waiting for a response, here are some tips so you can get the most out of the forum and other Enterprise DNA resources.

  • Use the forum search to discover if your query has been asked before by another member.
  • When posting a topic with formula make sure that it is correctly formatted to preformatted text </>.
    image
  • Use the proper category that best describes your topic
  • Provide as much context to a question as possible.
  • Include the masked demo pbix file, images of the entire scenario you are dealing with, screenshot of the data model, details of how you want to visualize a result, and any other supporting links and details.

I also suggest that you check the forum guideline How To Use The Enterprise DNA Support Forum. Not adhering to it may sometimes cause delay in getting an answer.

Please also check the How To Mask Sensitive Data thread for some tips on how to mask your pbix file.

4

Apologies, That was a typo. the tank in this scenario would be 200,000 litres and accepting loads of fuel on different days and at different purchase prices. The challenge for me is to add my markup for the litres purchased and once those have been consumed, to adjust my pricing to reflect the new purchase price for the next quantity that was put into the tank.

I don’t have a PBIX to share as I have been using a moving average price instead of something that is tied to the actual price paid for the given quantity.

Scott

5

Hi @smarta. As with most things in Power BI, there are probably multiple ways of doing the same thing. Here’s one possible solution (if I understood your scenario correctly).

eDNA Forum - Commodity Pricing 1
eDNA Forum - Commodity Pricing 11631×1223 177 KB

To present this, I:

  • created separate tables for [Purchases] and [Sales}
  • added a calculated column to each (Cumulative Volume)
  • added a DATES table and marked it as such (used the Extended Date Table from eDNA, Extended Date Table (Power Query M function))
  • added 1:many relationships from Dates[Date] to Purchases[Date] and Sales{Date]
  • added [Markup] measure = $0.25
  • added [Target Cost] measure
Target Cost = 
VAR _CumulativeVolume = SELECTEDVALUE( Sales[Cumulative Sales Volume] ) 
VAR _AllPurchasesOverCumulative = FILTER(
    Purchases,
    Purchases[Cumulative Purchase Volume] >= _CumulativeVolume
) 
VAR _AssociatedPurchase = TOPN(
    1,
    _AllPurchasesOverCumulative,
    Purchases[Cumulative Purchase Volume],
    ASC
) 
VAR _Result = IF(
    HASONEVALUE( Sales[ID] ),
    CALCULATE( MAX( Purchases[Cost] ), _AssociatedPurchase ),
    BLANK()
) 

RETURN
_Result

Hope this helps.
Greg
eDNA Forum - Commodity Pricing.pbix (88.5 KB)
eDNA Forum - Commodity Pricing.xlsx (12.3 KB)

Notes:

  • tested with made-up sample data only
  • does not account for “mixed” purchases (i.e., where part of the sale is at one price and the remainder of the sale at a different price [in example, see sale 112])
  • to use the sample without change, both PBIX and EXCEL files should be downloaded to the local C:\Temp\ folder
3 Likes
6

Greg:

This is a brilliant AND elegant solution. I was definitely over thinking this and the clarity that you have shown in your solution is perfect. In a million years, I wouldn’t have come up with the VAR statements that you created.

Thank you so very much!!!