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

~ Business Intelligist

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], ", ")
)

Published by Business Intelligist

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

    Reply

  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.

    Reply

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

    Reply

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

    Reply

  7. I have a cost history table that includes product, date range, but multiple price points for quantity discount pricing. Do you have any suggestions on how to summarize the sales by Net Price and then assign those sales to a suggested price point? I would want to see sales totaled at the within the different suggested price buckets.

    For example:

    Product Net Price Sales
    x 19.99 50
    x 17.99 50

    Product Suggested Price Date Effective
    x 20.99 1/1/2022 – 12/31/2022
    x 19.99 1/1/2022 – 12/31/2022
    x 18.99 1/1/2022 – 12/31/2022

    Output Suggested Price Sales
    x 19.99 50
    x 18.99 50

    Reply

Leave a comment