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