cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Frequent Visitor

## Fetch Cost Price based on Sales Date

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.

1 ACCEPTED SOLUTION
Super User

@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

3 REPLIES 3
Super User

@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

Frequent Visitor

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!

Frequent Visitor

Thank you very much, this worked!

Announcements

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

#### Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

#### Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors