Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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

Reply
QPV
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
amitchandak
Super User
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

 

 

View solution in original post

3 REPLIES 3
amitchandak
Super User
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

 

 

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!

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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

MayPowerBICarousel

Power BI Monthly Update - May 2024

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

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.