Explore and share Fabric Notebooks to boost Power BI insights in the new community notebooks gallery.
Check it out now!Microsoft is giving away 50,000 FREE Microsoft Certification exam vouchers. Get Fabric certified for FREE! Learn more
Hi everyone,
I receive cost price lists from a factory every six months (or more, occasionally) in an Excel file. I also have an output from our accounting software with the sales per order. I imported both to PowerBI and want to connect them in order to analyze the margins on every order, article, period, etc.
In order for me to view the margin on our sales correctly, I need to add a column to my sales table that fetches the correct cost price, based on the cost price table. Without this column, any views of sales will have a cost price of 0 (unless they happened on the exact day that cost prices go in, which is the first of April or October usually).
Searching online, I found the below code to add this column:
Cost Price =
VAR CostPrice =
CALCULATETABLE (
CALCULATETABLE (
'000_ImportAVK',
FILTER (
ALL ( '300_Dates' ),
'300_Dates'[Date] <= MAX ( '300_Dates'[Date] )
)
),
FILTER ( '000_ImportAVK', '000_ImportAVK'[Article Code] = '100_Sales'[Article Code] )
)
VAR Maxdate =
CALCULATE ( MAX ( '300_Dates'[Date] ), CostPrice )
RETURN
CALCULATE (
MAX ( '000_ImportAVK'[Cost Price] ),
'300_Dates'[Date] = Maxdate,
CostPrice
)
However, this only gives me the most recent cost price for every order, even if that order is from an earlier date. Is there any way in which it is possible to show the actual cost price of that date?
Any help is appreciated greatly.
Solved! Go to Solution.
@QPV , Assume Cost has cost price with date, item and sales has sales date ans item
then a new column in sales table
=
var _max = maxx(filter(Cost, cost[Item] = sales[Item] && cost[Cost Date]<= sales[Sales Date]), COst[Cost Date])
return
maxx(filter(Cost, cost[Item] = sales[Item] && cost[Cost Date]=_max), COst[Cost Date])
Change as per need
@QPV , Assume Cost has cost price with date, item and sales has sales date ans item
then a new column in sales table
=
var _max = maxx(filter(Cost, cost[Item] = sales[Item] && cost[Cost Date]<= sales[Sales Date]), COst[Cost Date])
return
maxx(filter(Cost, cost[Item] = sales[Item] && cost[Cost Date]=_max), COst[Cost Date])
Change as per need
Dear @amitchandak,
Is something like this possible for reports that use DirectQuery as well? I am trying to recreate the report with DirectQuery so I don't have to manually refresh and re-upload the report for my viewers.
Thanks!
Thank you very much, this worked!