Calculate Cost price based on the sales order

[1320Data]

I have different ERP system to maintain cost price and CRM for sales order. I am bringing different source system into my azure Data warehouse (one source of truth), when I created a data model with Dim salesorder, Fact Sales order (Which contains details of sales happened, sales order id, gross sales ,discount , calendar id etc) whereas Fact Production data contains sales order id, cost price , calendar id. I also have DIm calendar which contains time stamps, calendar id that connects all table and sales order id that connects dim and facts.

The problem is I am trying to get the cost price for the particular sales order id, where the machine was built in 2021 and sold in 2022. I am getting the cost price of only when the product is sold and not the cost price of when the machine started to built.

I tried DAX measure to remove the filter to calulate cost price, it works but the cost price remains same for all years irrespective what I select in Calendar. I want to know how to calculate the cost price totally for the particular sales order id and once the sale is over in 2022 , it should not appear in 2023.
Look the image below (Machine built in 2021-2022 and sold in 2022)

How can  I make the right dax measure?

Total Cost Price All Periods =

CALCULATE(

    SUM('Fact Production'[Basic Currency Amount Total]),

   ALLSELECTED('Dim Calendar')

)

[Anonymous · Accepted solution]

Hi @1320Data ,

According to your description, here are my steps you can follow as a solution.

(1) This is my test data.

Dim Calendar = CALENDAR(DATE(2021,1,1),DATE(2023,12,31))

(2) We can create a measure. 

Total Cost Price All Periods = CALCULATE(SUM('Fact Production'[Basic Currency Amount Total]),FILTER(ALLSELECTED('Dim Calendar'),YEAR('Dim Calendar'[Date])=YEAR(MAX('Fact Production'[date]))))

(3) Then the result is as follows.

If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. Thank you.

Best Regards,

Neeko Tang

If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly.