How to Calculate Revenue and Margin Using Price and Cost History Tables (Power BI DAX Tutorial)

I use Sales data a lot in my tutorials and usually the Sales Fact table will have the following fields in it:

  1. Date
  2. Product
  3. Revenue
  4. Units

Along with a bunch of other stuff….

Now, it is actually very common for a company to only provide Units in the Sales Table along with a Price History and/or Cost History tables that might look like this:

Can we derive the revenue and cost on the fly? Of course, we can! Please see the video below for details (DAX formulas are also included at the end of the post)

You can download the Power BI model used in this video here.

Spot Price = 

var vDate = SELECTEDVALUE(Sales[Date])
var vProduct = SELECTEDVALUE(Sales[ProductID])

RETURN 
CALCULATE(
    MAX('Price History'[Price]),
    FILTER(
        ALL('Price History'),
        'Price History'[ProductID] = vProduct
        &&
        'Price History'[EffectiveFrom]<=vDate 
        && 
        'Price History'[EffectiveTo]>=vDate
    )
)
Spot Unit Cost = 

var vDate = SELECTEDVALUE(Sales[Date])
var vProduct = SELECTEDVALUE(Sales[ProductID])

RETURN 
CALCULATE(
    MAX('Unit Cost History'[Unit Cost]),
    FILTER(
        ALL('Unit Cost History'),
        'Unit Cost History'[ProductID] = vProduct
        &&
        'Unit Cost History'[EffectiveFrom]<=vDate 
        && 
        'Unit Cost History'[EffectiveTo]>=vDate
    )
)
Revenue = 
SUMX(
    Sales,
    Sales[Units]*[Spot Price]
)
Cost = 
SUMX(
    Sales,
    Sales[Units]*[Spot Unit Cost]
)
Margin = [Revenue] - [Cost]
Sales Trend Title = 
"■ UNITS AND ▬ REVENUE TREND FOR PRODUCT(S): " &
SELECTEDVALUE(
   'Product'[Product], 
   CONCATENATEX('Product', 'Product'[Product], ", ")
)

6 thoughts on “How to Calculate Revenue and Margin Using Price and Cost History Tables (Power BI DAX Tutorial)

  1. Usually I never comment on blogs but your article is so convincing that I never stop myself from saying something about it. You’re doing a great job Man,Keep it up.

  2. Usually I never comment on blogs but your article is so convincing that I never stop myself from saying something about it. You’re doing a great job Man,Keep it up.

  3. Usually, I never comment on blogs but your article is so convincing that I never stop myself from saying something about it. You’re doing a great job Man, Keep it up.

  4. I found your post interesting to read. I can’t wait to see your post soon. Good Luck with the upcoming update. This article is really very interesting and effective.

Leave a Reply to steven jones Cancel reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s