Forum Discussion
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.
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 🙂