SCD (Type 2) for Profit calculation

[Dor-Y13]

Hello friends,

I have a star schema with Fact table that is a Stock movements table=StockMoves

one of my Dimensions is my cost of Items (each item has a startdate and enddate for its relevant price)  - [Table name = PricesforSCD]

I want to calculate for each sale date the gross profit which is relevant for the sale date according to the relevant cost price in that range.

Could you please help me with the DAX correct measure? 

I guess that a measure that reflects the correct cost will be sufficient for making calculations per sale.

Many thanks,

Dor

[Dor-Y13]

I have managed to insert an long term end date so it will not interfere.
Your solution is great thanks a lot!

[Dor-Y13]

Hello, thanks a lot for your elaborated answer, it did not work yet - hope you can help with adjustments and figure why it doesnt work yet:
My end date is a calculation made on my cost table (since i have only start date in the data), so the Measure of end date is (also used power query for this solution):

EndDate = 
VAR _Index = [Index]+1
VAR _Id = [ItemKey] 
RETURN CALCULATE( MAX(PricesForSCD[DatF - StartDate]), 
                FILTER(ALL(PricesForSCD),
                 PricesForSCD[Index] = _Index && [ItemKey] = _Id ))

The outcome looks good (need to notice that the updated price is always with a blank value on the end date since it was not ended yet): thats a 1 ItemKey outcome:

Once i have written the DAX formula you wrote i tried to relate to the issue with the blank end date but with no success, thats the formula with relevant columns (my attemps to relate to the blank enddate were not successful):

Profit SCD = 
                    VAR saledate = MAX(Stock[ValueDate - תאריך אסמכתא])
                    VAR ItemID = MAX(StockMoves[ItemKey])
                    VAR costprice = CALCULATE(
                                                MAX(PricesForSCD[Price]),
                                                PricesForSCD[DatF - StartDate]< saledate, 
                                               PricesForSCD[EndDate]>= saledate,
                                                PricesForSCD[ItemKey] = ItemID
                                    )
                    RETURN
                    SUMX(StockMoves,
                        ([Total sales Inc. returns] - costprice) * [Total Quantity sold including returns]
                    )
                  

Could you please suggest how to relate to the Blank End date?
Maybe it will be better to create a mesaure that computes the relevant cost price as a first step?

that is the ERROR im getting now when i am trying to show a visual for the Measure (i have checked and the date columns are marked as dates, Itemkey as Number):

Hope you could help me solve that one,

Thanks a lot 🙂

[Anonymous · Accepted solution]

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. 

[Dor-Y13]

@Anonymous

Hi friend, could you please try to comment regard my reply?

Do you think it will be Better to try to add the cost price to the FACT table by using calculated column? 

[Anonymous]

Hi @Dor-Y13 ,

What do you want to do with a null enddate? You can use the ISBLANK function in conjunction with the IF function within the measure to do something when the enddate is null.

I don't quite understand your logic here, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. 

Refer: 

How to provide sample data in the Power BI Forum

How to Get Your Question Answered Quickly

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.