I use Sales data a lot in my tutorials and usually the Sales Fact table will have the following fields in it:
- Date
- Product
- Revenue
- 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], ", ")
)
business intelligist 
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.
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.
Very interesting , good job and thanks for sharing such a good blog.
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.
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.
Found your post interesting to read. I can’t wait to see your post soon. Good Luck for the upcoming update.
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