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

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

1. Russel David says:

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. Russel David says:

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. Russel David says:

Very interesting , good job and thanks for sharing such a good blog.

4. Russel David says:

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.

5. Russel David says:

6. Found your post interesting to read. I can’t wait to see your post soon. Good Luck for the upcoming update.

7. Alan says:

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