SCD (Type 2) for Profit calculation

Hi Dor-Y13 ,

1. Create Relationships: Ensure that there is a relationship between your `StockMoves` fact table and the `PricesforSCD` dimension table. The relationship should be based on the item identifier.

2. Create a Measure.

Gross Profit Measure = 
VAR SaleDate = MAX(StockMoves[SalesDate]) -- Replace with your actual sales date column
VAR ItemID = MAX(StockMoves[ItemID]) -- Replace with your actual item identifier column
VAR CostPrice =
    CALCULATE (
        MAX(PricesforSCD[CostPrice]), -- Replace with your actual cost price column
        PricesforSCD[StartDate] <= SaleDate,
        PricesforSCD[EndDate] >= SaleDate,
        PricesforSCD[ItemID] = ItemID
    )
RETURN
SUMX(
    StockMoves,
    (StockMoves[SalesAmount] - CostPrice) * StockMoves[Quantity] -- Replace with your actual sales amount and quantity columns
)

Please Note: The DAX provided is a basic template and might need to be adjusted based on the exact structure of your data model and business logic.

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.